0

We would like to connect securely to SQL 2008 R2 over the internet for reporting purposes. Speed is not a huge concern for these smaller data sets, and users are going to be using simple reporting programs (e.g. Access) that require ODBC connections, rather than an API of sorts. In this scenario we have a SQL Server running on a local network (e.g. sql.mydomain.local) which is being accessed from the outside world via NAT (e.g. sql.mydomain.com, port xyz). This works fine with no security, however we need to encrypt the connections. The simple way to do that is to add SSL to the SQL server. Here is where I run into issues though:

  1. If I get a certificate for the external name (sql.mydomain.com), SQL Server refuses to see or use the external SSL cert as it is not the servers name or FQDN, since it is configured locally (per http://technet.microsoft.com/en-us/library/ms191192.aspx). Manually adding the cert via HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib doesn’t seem to change that behavior for me.
  2. If I use a self-signed certificate from the domain controller, I’m able to see and use the cert in SQL. However external connections obviously complain that the cert is not trusted. If I add the domain controller as a trusted publisher, then SQL complains that the server name doesn’t match the certificate name when I attempt to connect to it.

All of the paths forward seem a little wonky: 1. Setup a local domain for only that server with the public name (e.g. mydomain.com) and add the machine there so that the FQDN matches the external certificate generated from a trusted publisher, hopefully SQL will see and use the cert then. 2. Have the users modify their hosts file to create a cname of sql.mydomain.local to sql.mydomain.com.

Am I missing something in my understanding so far? Is there any way to get around SQL’s limitations regarding use of a cert which is not the FQDN? If I purchase a certificate for sql.mydomain.com with a Subject Alternative Name of sql.mydomain.local, will SQL see and use it?

cninsd
  • 41
  • 1
  • 5

1 Answers1

2

The answer to this issue is to use Subject Alternative Name certificates so SQL will be happy. I tested using a domain issued certificate, following some excellent steps here: http://williamdurkin.com/2013/03/sql-server-connection-encryption-and-net-framework-4-5/

That generated certificate was able to be seen and used by SQL Server, and once the domain cert was added as a trusted publisher, the client was able to connect securely.

cninsd
  • 41
  • 1
  • 5
  • Note that SAN certificates only seem to be working with the SQL Native v11 (SQL2012) connections – cninsd Sep 06 '13 at 18:58