This was posted just to share an experience of tackling a problem we encountered when we needed to connect an SSRS Server 2016 to an older SQL Server database (Windows Server 2008 R2) and what steps we took to troubleshoot the issue.
After all the reports were migrated to the new SSRS server, upon creating a new Data Source, I got an error message something along the line to enter the user id and password correctly.
A simple way to check the connection to the SQL Server database is by setting up an ODBC connection to test.
Sure enough, even the ODBC Data Source Test failed with this message:
Microsoft SQL Server Login Connection failed: SQL State: '01000' SQL Server Error: 1 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (SECCreateCredentials()). Connection failed: SQLState: '08001' SQL Server Error: 18 [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error
For the first issue, the resolution was because of the difference of TLS version enabled by default for each server. We know that TLS 1.2 is enabled by default on Windows Server 2016 or later. However, for the Windows 2008 R2 was not set up correctly.
No TLS enabled at all!
After following the steps to enable TLS 1.2 on Windows Server 2008, we retried the ODBC connection from the new SSRS server (Windows Server 2016) and it was successful.
With the ODBC connection working, we went back to check the connection from the SSRS itself (the same Windows 2016 Server). But when we clicked on Test connection, we still encountered the following error:
Couldn't connect The report server couldn't connect to the data source using the information you entered. Make sure you've entered the connection string and any credentials correctly. Hide error details ^ A connection was successfully established with the server, but then an error occured during the login process. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)
Logically, both servers should be able to communicate with each other using TLS 1.2 since both were enabled, but this could be caused by the weak cipher on the old Windows 2008. Since we were pressed on time and the client agreed with the temporary risk, the solution that we opted was to enable both TLS 1.0 and TLS 1.1 on both servers in addition to TLS 1.2.
Had these servers reside on the DMZ, we won’t recommend this solution at all. Even for internal servers such as our clients, we don’t recommend this solution as a long-term fix. But in this case, it was good enough.
This was remediated soon after when the client upgraded the database server to a Windows Server 2019, where TLS 1.2 is also enabled by default with strong ciphers being available.
Hopefully, it will help others who are also in the intermediary state of upgrading and trying to troubleshoot a similar issue.
With that, I welcome any input for a better solution (i.e., you have time to test that it only requires TLS 1.1 but not 1.0, or something more secure for that matter).
Further Reading
How to Enable TLS 1.2 as the Default Security Protocol on Windows Servers
Leave a Reply