locked
Encrypting communication between an app that uses an ODBC/DSN (with ADODB) and SQL Server 2008 R2 RRS feed

  • Question

  • I've been doing a lot of reading the last couple of days on how we can encrypt db communication between our product app and a customer's SQL Server db, but cannot make it work as expected. The app uses a ODBC/DSN to connect to the SQL Server db. I use this ODBC app to setup the DSN (on a Windows 7 PC):

    C:\Windows\SysWOW64\odbcad32.exe

    The  DSNconnection uses the SQL Server driver 6.01.7601.17514 and has these properties:

    - WinNT authentication.
    - Client Config button: TCP/IP to <server-name>\<instance.-name>
    - Change to default db: <name-of-app-db>
    - Everything else is default setting.

    SQL Server is on the same Windowns 7 PC and has a self-signed cert installed (used IIS to generate it) and has the Force Enryption set to "yes".

    I have a test C# program that uses the ADODB 2.7.0.0 COM-wrapper, made by Visual Studio after adding a reference to the ADO 2.7 library version 6.1.7601.17857. The program creates an ADODB.Connection object that has a simple connection string: "DSN=<san-name>;UID=<user>;PWD=<password>". The program then creates an ADODB.Recordset object and reads and displays a field from a table.

    Works fine.

    If I go into SQL Server and set Force Encryption to "no," clear the cert, restart the SQL service, and then re-run the program, it works fine.

    Here's the kick. If I go into the DSN and select "Use strong encryption for data" the Test button on the DSN works—why does it work? The SQL Server is no longer encrypting the connection so an error should occur. If I run the test program, it works as well—why? I can look at the connection properties in the test program and see that ADODB has added the ";Encrypt-yes" stuff to the end of the connection string. Yet that option seems to have no effect.

    If I set the SQL instance back to Force Encryption:yes, enable the cert, restart the SQL service, and clear the DSN's "Use strong encryption for data" option, I can still connect to the db with the test program—why?

    What am I doing wrong? I need to be able to ensure that the communication between our app product and the SQL instance is encrypted, and that we get an error if the SQL instance does not support encrypted communications. We really don't want the customer to have to enable Force Encryption because they have other db's on their SQL Server that do not use encrypted communication, but they want to know that our product's communication channel with the db is encrypted.

    No, I can't change the app product's code at this point in time. I'm stuck with working with what a DSN called from ADODB has to offer.

    Also, how can I be sure that communications are encrypted? I mean, I've tried things like "SELECT * FROM sys.dm_exec_connections" but that doesn't help because I have no idea how to tie the list of sessions shown back to my test program, although I guess it's a good thing that some of the sessions listed show encrypt_option as TRUE.


    -glenn-

    Tuesday, August 19, 2014 5:12 PM

Answers

  • Hmm.  For some reason you are trusting the self-signed cert.  Perhaps there's some Windows config that controls this.

    In any case, with SQL Native Client you can specify both Encrypt=Yes and TrustServerCertificate=Yes in the DSN definition, with either a file-based DSN or a system or user DSN.  The ODBC Administrator tool doesn't support that keyword, but you can add it directly in the registry or the file.

    A file DSN like

    [ODBC]
    DRIVER=SQL Server Native Client 11.0
    WSID=DBROWNE2
    APP=Microsoft® Windows® Operating System
    Trusted_Connection=No
    SERVER=sql
    Encrypt=yes
    TrustServerCertificate=yes

    Or creating or modifying a 32bit System DSN through Regedit or powershell.  ODBC System DSNs are stored under HKLM:\SOFTWARE\ODBC\ODBC.ini

    If you're running 32bit Regedit you'll see the 32bit DSNs at that path, but if you're running 64bit Regedit you'll see the 32bit DSNs under HKLM:\Software\Wow6432Node\ODBC\ODBC.INI

    Under the key for your DSN create a new String Value (REG_SZ) with name TrustServerCertificate and Yes for the value.

    Eg here's how to add this property to an existing 32bit system DSN called MyDsn in one line of Powershell:

    
    
    PS C:\> set-itemproperty -Path HKLM:\Software\Wow6432Node\ODBC\ODBC.INI\MyDsn -name TrustServerCertificate -value Yes
    

    This is for 64bit powershell.  In 32bit powershell, or for setting a 64bit DSN in 64bit powershell you would just use:

    
    
    PS C:\> set-itemproperty -Path HKLM:\Software\ODBC\ODBC.INI\MyDsn -name TrustServerCertificate -value Yes
    
    

    David


    David http://blogs.msdn.com/b/dbrowne/



    Thursday, August 21, 2014 9:27 PM

All replies

  • Clients may request encryption or not, and may trust the server cert, or not.  The Server may force encryption, or not, and may or may not have a cert configured.

    The scenarios for the different combinations of these are enumerated here: Using Encryption Without Validation

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Sofiya Li Wednesday, August 20, 2014 7:24 AM
    Tuesday, August 19, 2014 7:51 PM
  • Thanks, I've seen that table. What I've described in words is essentially the second row of that table. The client has Encrypt=yes and no "Trust Server Cert" keyword in the connection string, yet SQL Server let's the communication take place without a server cert (and Force Encryption is not enabled) - that is, I should get "otherwise the connection attempt fails" but I don't.

    It's tricky too because there's no way to explicitly verify that the communication channel is indeed encrypted, or is not.


    -glenn-

    Tuesday, August 19, 2014 8:17 PM
  • Hmm.  I can't repro.  For all my ODBC drivers I get:

    Attempting connection
    [Microsoft][ODBC Driver 11 for SQL Server]SSL Provider: The certificate chain was issued by an authority that is not trusted.

    [Microsoft][SQL Server Native Client 10.0]SSL Provider: The certificate chain was issued by an authority that is not trusted.

    Attempting connection
    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SSL Security error

    The old SQL Server driver for me is 6.03.9600.16384, but I doubt that's actually been changed.  That ODBC driver is deprecated and very old, can you switch to a newer ODBC driver, like SNAC or Microsoft® ODBC Driver 11 for SQL Server

    And you should be able to identify the connections by what database they are connected to.  eg

    select c.session_id, c.encrypt_option, s.client_interface_name
    from sys.dm_exec_connections c
    join sys.dm_exec_sessions s
      on c.session_id = s.session_id
    where s.database_id = db_id('MyDatabase')

    David


    David http://blogs.msdn.com/b/dbrowne/



    Tuesday, August 19, 2014 8:43 PM
  • Here's the tests I just tried again. In all of them, the SQL Server has Force Encruption disabled.

    1. SQL Server has no cert defined. Whether the DSN has "Use strong encruption" enabled or not, SQL Server lets the connection be made, and I never get the "cert chain" error.

    2. Enable the self-signed cert in SQL Server, same results as #1 - Encypt=yes and Encrypt=no both let me connect and never get the "cert chain" error. I do see in the Event Log that the cert has been loaded:

    Event ID 36013: The certificate [Cert Hash(sha1) "3695842064AE3296DF7B1A32BDC9873C98470629"] was successfully loaded for encryption.

    The connection string I'm using is still simpy: "DSN=...;UID=...;PWD=...;". When I look at the "errors" from the ADODB.Connection after the Open call, I see:

    *** 1: Error # 0: [Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to '...'.; SQL State: 01000; Native: 5701; Source: Microsoft OLE DB Provider for ODBC Drivers

    *** 2: Error # 0: [Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english.; SQL State: 01000; Native: 5703; Source: Microsoft OLE DB Provider for ODBC Drivers

    Those look fine to me.

    And the "Extended Properties" item in the Properties collection of the ADODB.Connection shows this:

    Extended Properties = DSN=...;Description=...;UID=...;PWD=...;Trusted_Connection=Yes;APP
    =CSharpConsolePlayground;WSID=...;DATABASE=...;Encrypt=Yes

    When I uncheck "use strong encryption" in the DSN, the ";Encrypt=Yes" option does not appear at the end of the Extentend Properties.

    A couple of the other connection properties after the Open, for FYI, are:

    - DBMS Version = 10.50.4033
    - Provider Name = MSDASQL.DLL
    - OLE DB Version = 02.00
    - Provider Version = 06.01.7601
    - User Name = dbo
    - Server Name = (points to correct server\instance)
    - Driver Name = SQLSRV32.DLL
    - Driver Version = 06.01.7601
    - Driver ODBC Version = 03.52

    (Sure wish there was a connection property along the lines of "Using Encrypted Connection: yes/no".)

    Trying to think of things to try, so I changed the ADODB reference in the C# test program from ADO 2.7 to ADO 2.8 (file version 6.1.7601.17857). Same results. So then tried ADO 6.0 and then 6.1 (all have same file version 6.1.7601.17857) with same results.

    I tried to install the ODBC Driver 11 for SQL Server that you linked to, but got this:

    "Installation of ODBC Driver 11 for SQL Server failed because a higher version already exists on the machine."

    Here's the versions I could find:

    - C:\Windows\System32\sqlsrv32.dll = 6.1.7601.17514
    - C:\Windows\SysWOW64\sqlsrv32.dll = (same)
    - C:\Windows\winxs\amd64... and x86... = (same)

    Also, what am I doing wrong with the session query you posted? There doesn't appear to be a database_id field in the dm_exec_sessions table (or in the dm_exec_connections table). If I do "SELECT *" from each table, the db name does not appear in any of the columns.

    What is your config to trigger the "cert chain" error? In particular, what were these settings:

    - Force Encryption?
    - Cert loaded: none, auto-generated, self-signed, other?
    - What's your client and its settings: C# program, Management Studio, other?
    - Anything else?

    FYI, I can't even trigger the "cert chain" error when trying to connect from Management Studio when I register a server with Encrypt Conection enabled/disabled and/or using the computer-name\instance-name or the fqdn-cert-name\instance-name or the Force Encryption yes/no. What's up with that? Clearly I must be doing something wrong.

    Thank you for your help with this!


    -glenn-

    Wednesday, August 20, 2014 1:21 PM
  • * 1: Error # 0: [Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to '...'.; SQL State: 01000; Native: 5701; Source: Microsoft OLE DB Provider for ODBC Drivers

    This means that you are using the old ODBC driver that shipped with SQL 2000 (or something even older).

    I tried to install the ODBC Driver 11 for SQL Server that you linked to, but got this:

    "Installation of ODBC Driver 11 for SQL Server failed because a higher version already exists on the machine."

    So maybe you have the driver - but you are not using it. You need to change the connection string to say Driver={SQL Server Native Client 11}.

    Also, what am I doing wrong with the session query you posted? There doesn't appear to be a database_id field in the dm_exec_sessions table (or in the dm_exec_connections table). If I do "SELECT *" from each table, the db name does not appear in any of the columns.

    You are doing nothing wrong. David failed to observe that you are on SQL 2008 R2, and he is using a column added in SQL 2012.

    You can use this:

    select c.session_id, c.encrypt_option, s.client_interface_name
    from sys.dm_exec_connections c
    join sys.sysprocesses s
      on c.session_id = s.session_id
    where s.dbid = db_id('MyDatabase')


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, August 20, 2014 9:33 PM
  • I can't change the connection string. The app references a DSN in the connection string, and I cannot change the code out in the field. Again, the connection string is this:

    DSN=<dsn-name>;UID=<user-id>;PWD=<password>

    It is not possible for us to change the code that builds this connection string at this point in time. That's why this thread is titled "ODBC/DSN with ADODB". Oh, how I wish I code change the code; I'd change a lot more than the connection string! But this legacy code must still be supported.

    So the only chance I have in getting an encrypted connection to work is to tweak a setting in the DSN. I'm really just trying to understand how to get the DSN option "Use strong encryption" to work (it automatically adds the Encrypt=yes to the Extended Properties), and how to make it give the "change cert/unable to use SSL" error when "Use strong encryption" option is enabled (i.e., we do not want the connection to fall back to non-encrypted when the SQL Server does not have a cert installed, we want the error like David showed).

    Maybe we are just SOL unless we change our code. The thing is that the driver being used may be old but it works just fine for I/O with our installed base, except in this case where we now have some clients that want the connection to be encrypted between our product app and SQL Server.

    (I don't think that driver goes back to SQL Server 2000; pretty sure it's circa SQL Server 2005.)

    Plus, even if I could change the connection string, it doesn't explain why I can setup a registered server in SQL Management Studio that has "Encrypt Connection" enabled, but still allows me to connect to a SQL Server instance that does not have a cert installed. Doesn't the "Encrypt Connection" option mean that I should get the "change cert/unable to use SSL" error when "Encrypt Connection" is enabled but the target SQL instance does not have a cert installed?

    Thanks.


    -glenn-

    Wednesday, August 20, 2014 11:51 PM
  • > The app references a DSN in the connection string, and I cannot change the code out in the field.

    And the DSN specifies the ODBC driver to use.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, August 21, 2014 1:36 PM
  • Ah yes, very good point. It's easy to miss because you have to delete then recreate the DSN in order to change drivers. So I switched the DSN over to the SQL Native 11 driver.

    Now when I try to connect to the SQL instance as <computer-name>\<instance-name>, and without a cert on the server, I get "the target principal name is incorrect". Perfect; now we're getting somewhere!

    Change my DSN to use <fqdn>\<instance-name> and it works. This tells me that SQL Server has auto-generated a cert and named it <fqdn>. I would have expected the "cert fail" error, not a cert name mismatch, because I'm not using Trust Server Cert.

    So I load up my self-signed cert, and that works too.

    I am still confused as to why I'm not seeing the "cert fail" error when I have no cert loaded on the SQL Server. I am not using Force Encryption on the server at all, so wouldn't expect SQL Server to auto-create a cert when an Encrypt=yes request comes in, but apparently it does?

    I also ran into a problem with this:

    select c.session_id, c.encrypt_option, s.client_interface_name
    from sys.dm_exec_connections c
    join sys.sysprocesses s
      on c.session_id = s.session_id
    where s.dbid = db_id('MyDatabase')

    There is no s.client_interface_name, probably should be s.hostname. There's also no s.session_id. I thought maybe this should be s.sid, but then no rows ever come back. The c.session_id looks like 51 and 52, but the s.sid looks like a very long binary number, so these two fields cannot be joined. I don't know how to convert the sid's properly so that the join would work. Ah wait, I just found the s.spid column; the join works when that column is used (I assume that's correct anyhow).

    If I add a Thread.Sleep(30 seconds) to my C# program just before the connection is closed, this query shows me the session for the correct hostname has encrypt_option=TRUE.

    And I have to keep my fingers crossed that all the app I/O will still work properly after changing the driver. Probably a safe bet though.

    Think I'm ready to throw in the towel on getting the "cert fail/no SSL" error to appear. It does look like I am able to sufficiently show that the connection is encrypted when Encrypt=yes is used with the newer driver.

    Thank for all the help!


    -glenn-

    Thursday, August 21, 2014 2:54 PM
  • I also ran into a problem with this:

    select c.session_id, c.encrypt_option, s.client_interface_name
    from sys.dm_exec_connections c
    join sys.sysprocesses s
      on c.session_id = s.session_id
    where s.dbid = db_id('MyDatabase')

    Sorry, let's try this again:

    select c.session_id, c.encrypt_option, s.client_interface_name
    from sys.dm_exec_connections c
    join sys.sysprocesses sp
       on c.session_id = sp.spid
    join sys.dm_exec_sessions s ON s.session_id = c.session_id
    where sp.dbid = db_id('MyDatabase')


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 21, 2014 9:13 PM
  • Hmm.  For some reason you are trusting the self-signed cert.  Perhaps there's some Windows config that controls this.

    In any case, with SQL Native Client you can specify both Encrypt=Yes and TrustServerCertificate=Yes in the DSN definition, with either a file-based DSN or a system or user DSN.  The ODBC Administrator tool doesn't support that keyword, but you can add it directly in the registry or the file.

    A file DSN like

    [ODBC]
    DRIVER=SQL Server Native Client 11.0
    WSID=DBROWNE2
    APP=Microsoft® Windows® Operating System
    Trusted_Connection=No
    SERVER=sql
    Encrypt=yes
    TrustServerCertificate=yes

    Or creating or modifying a 32bit System DSN through Regedit or powershell.  ODBC System DSNs are stored under HKLM:\SOFTWARE\ODBC\ODBC.ini

    If you're running 32bit Regedit you'll see the 32bit DSNs at that path, but if you're running 64bit Regedit you'll see the 32bit DSNs under HKLM:\Software\Wow6432Node\ODBC\ODBC.INI

    Under the key for your DSN create a new String Value (REG_SZ) with name TrustServerCertificate and Yes for the value.

    Eg here's how to add this property to an existing 32bit system DSN called MyDsn in one line of Powershell:

    
    
    PS C:\> set-itemproperty -Path HKLM:\Software\Wow6432Node\ODBC\ODBC.INI\MyDsn -name TrustServerCertificate -value Yes
    

    This is for 64bit powershell.  In 32bit powershell, or for setting a 64bit DSN in 64bit powershell you would just use:

    
    
    PS C:\> set-itemproperty -Path HKLM:\Software\ODBC\ODBC.INI\MyDsn -name TrustServerCertificate -value Yes
    
    

    David


    David http://blogs.msdn.com/b/dbrowne/



    Thursday, August 21, 2014 9:27 PM