IT Nota

  • Home
  • How To
  • .NET
  • WordPress
  • Contact
You are here: Home / Use Case / Connect New SSRS Server to Old SQL Server with TLS

Connect New SSRS Server to Old SQL Server with TLS

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:

ODBC Data Source Administrator Test Failed

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.

Windows Server 2008 registry with no TLS enabled

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.

ODBC Data Source Administrator Test 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:

SSRS Data Source Connection SSL/Algorithm/TLS 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.

Windows registry enable TLS 1.0, TLS 1.1 and 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

January 17, 2022 Filed Under: Use Case Tagged With: Information Security, Microsoft, Microsoft SQL Server, SQL Server, SSRS

Buy me a coffee?

Buy me a coffee If you find any of the articles or demos helpful, please consider supporting my work here, you'll have my big thanks!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Buy me a coffee Support this site
Buy Me a Coffee?

Categories

  • .NET
  • Coding
  • Cybersecurity
  • Database
  • How To
  • Internet
  • Multimedia
  • Photography
  • Programming
  • Resources
  • Review
  • Tips and Tricks
  • Use Case
  • WordPress
  • Writing

Recent Posts

  • How to Delay Shutdown or Restart on a Mac
  • How to Activate Built-in Web Server
  • SSIS Best Practice with Naming Conventions
  • How to Delay Shutdown or Restart on a Windows Server
  • How to Type Emoji Using Keyboard Shortcuts in Windows and macOS

Recent Posts

  • How to Delay Shutdown or Restart on a Mac
  • How to Activate Built-in Web Server
  • SSIS Best Practice with Naming Conventions
  • How to Delay Shutdown or Restart on a Windows Server
  • How to Type Emoji Using Keyboard Shortcuts in Windows and macOS

Tags

.NET ASP.NET Cdonts Dll Classic ASP Code Editor ETL FSharp Genesis Framework Git Google HP Asset Manager HTML5 Hugo IIS Information Security Internet Internet Information Services iOS JAMStack JavaScript Linux macOS Microsoft Microsoft SQL Server MVC PHP PowerShell Python Simple Mail Transfer Protocol Smtp Server SQL SQL Server SSIS SSMS SSRS Sublime Text Visual Studio Visual Studio Code VPN Web Server Windows Windows 8 Windows 10 Windows 2012 Windows Server

Copyright © 2011-2023 IT Nota. All rights reserved. Terms of Use | Privacy Policy | Disclosure