I've been looking to brush up on some of the security capabilities in recent version of SQL Server and I found myself playing with the encryption features for connecting to SQL Server.
I was getting a touch frustrated and confused at being unable to force SQL Server to deny my connection when using the Force Protocol Encryption to be true on the client. I'd opened SQL Server Configuration Manager (SSCM), right clicked the SQL Native Client and set the flag to be Yes. I was hoping to be see a connection refused error when connection to my remote server which had been started with a self signed certificate. However, I was able to connect successfully through SQL Server Management Studio (SSMS) and checking sys.dm_exec_connections showed that my connection was being made without encryption. To further muddy the waters, when forcing the encryption through SSMS using the options in the connection dialog box, i got an error i was hoping for:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)
The problem (some people will be thinking, its obvious!!) was that I was testing using SQL Server Management Studio and as SSMS is written in .Net it doesn't use the SNAC to connect to SQL Server at all and uses the more snazzy .NetSqlClient. This explains why changing the properties on the SNAC in SSCM doesn't affect connections made through SSMS.
A method of checking that the flag does force an encrypted connection (it does!) is to use the Import/Export wizard (Start/Run/DTSWizard.exe) to connect which appears to use the SNAC and you'll an error message similar to the following:
SSL Provider: The certificate chain was issued by an authority that is not trusted. (Microsoft SQL Server Native Client 10.0)
No comments:
Post a Comment