Admin: Force Encryption Flag

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)

T-SQL: Intellisense? Where's that new table?

Does this ring a bell?

You're developing in SSMS and you issue a CREATE TABLE command. You then (understandably) want to add some rows to your new table, so you pop open a new window, start typing your INSERT command and when intellisense pops up the list of tables, your new one isn't there.

No matter as you can easily refresh the cache by just hitting CTRL + SHIFT + R (or go Edit, Intellisense from the menu).

Now, the funny thing is that if you'd have just started typing your insert statements in the same window as the one where you created the table, the table would have been available in the intellisense cache. But new windows opened don't appear to reflect this - its as if each query connection keeps a list of new objects created and then adds these to the main local cache.

Lets get started!!

Recently, I've been spending a fair bit of time looking through http://sqlblog.com/ and have been both entertained and informed by the stuff i've read. So much so in fact that I got the urge to get "Blogging" myself and see if i could add anything useful to the SQL Server community that is obviously already thriving.

Now, i've attempted a blog before and found i couldn't fill it with enough information and my enthusiasm waned quite quickly but hopefully i can make a better attempt this time. I can't guarentee high frequency but i'll endeavour to make regular postings even if its just a snippet of cool code that may help someone out (or will just serve as a reminder to me in the future!).

Anyhow, lets see how it goes.