Upravit

Sdílet prostřednictvím

Connect to SQL Server with strict encryption

  • Článek

Applies to: SQL Server 2022 (16.x)

Strict connection encryption enforces good security practices and makes SQL Server traffic manageable by standard network appliances.

In this article, we'll show you how to connect to SQL Server 2022 (16.x) using the strict connection type.

Prerequisite

Connect to SQL Server using a .NET application

For information on building and connecting to SQL Server using the strict encryption type, see Connection String Syntax on how to properly build the connection string. For more information on the new connection string properties, see Additional changes to connection string encryption properties.

Connect using an ODBC DSN

You can test a connection with the Strict connection encryption type using an ODBC DSN to SQL Server.

  1. Search for the ODBC Data Sources app in Windows.

    Screenshot of the O D B C data sources app.

  2. Make sure you have the latest ODBC driver by looking in the Drivers tab of ODBC Data Source Administrator.

    Screenshot of available drivers.

  3. In the System DSN tab, select Add to create a DSN. Then select the ODBC Driver 18 for SQL Server. Select Finish. We're going to use this to test our connection.

  4. In the Create a New Data Source to SQL Server window, provide a name for this data source, and add your SQL Server 2022 (16.x) server name to Server. Select Next.

    Screenshot of creating a data source using the O D B C driver.

  5. Use all default values for all the settings until you get to the screen that has Connection Encryption. Select Strict. If the server name that you entered differs from that in the certificate or if the IP address is used instead, set HostName in certificate to the one used in your certificate. Select Finish.

    Screenshot showing the strict encryption type.

  6. When the dialog box ODBC Microsoft SQL Server Setup pops up, select the Test Data Source... button to test the connection. This should enforce the strict connection to SQL Server for this test.

You can also test the connection to SQL Server with strict encryption using the OLE DB Driver with Universal Data Link (UDL).

  1. To create a UDL file to test your connection, right-click on your desktop, and select New > Text Document. You'll need to change the extension from txt to udl. You can give the file any name you want.

    Note

    You'll need to be able to see the extension name in order to change the extension from txt to udl. If you cannot see the extension, you can enable viewing the extension by opening File Explorer > View > Show > File name extensions.

  2. Open the UDL file that you created, and go over to the Provider tab to select the Microsoft OLE DB Driver 19 for SQL Server. Select Next >>.

    Screenshot of the U D L provider screen.

  3. On the Connection tab, enter your SQL Server server name, and select the authentication method you use for logging into SQL Server.

    Screenshot of the U D L connection screen.

  4. In the Advanced tab, select Strict for Connection encryption. If the server name that you entered differs from that in the certificate or if the IP address is used instead, set Host name in certificate to the one used in your certificate. Go back to the Connection tab when you're done.

    Screenshot of the U D L advanced screen.

  5. Select Test Connection to test the connection with the strict connection encryption.

    Screenshot of the U D L connection screen and testing connection.

Remarks

If you see SSL certificate validation failed, validate that:

  • Server certificate is valid on the machine you're using for testing
  • At least one of the following is true:
    • Provider SQL Server matches CA name or one of the DNS names in the certificate.
    • HostNameInCertificate connection string property matches CA name or one of the DNS names in the certificate.

See also