Mitch Wheat - Treat the cause, not the symptoms!

If you’re not encrypting connections to SQL Azure (or any remote SQL Server instance), then you probably should.

Encrypted connections to SQL Server use SSL,  and that is about as secure as you can get (currently).

[Remember: SSL protects only the connection, i.e. the data as it is transmitted ‘on the wire’ between the client and SQL Server. It says nothing about how the data is actually stored on the server].

SSMS

When you open SSMS’s ‘Connect to Server’ dialog, click the bottom right ‘Options’ button, and make sure you tick the checkbox ‘Encrypt Connection’:

SQLCMD

Ensure you add the -N command line option. The -N switch is used by the client to request an encrypted connection. This option is equivalent to the ADO.net option ENCRYPT = true.

e.g.

sqlcmd –N –U username –P password  –S servername –d databasename –Q “SELECT * FROM myTable”

Linked Servers

When creating a linked server to SQL Azure,  the @provstr parameter must be set to 'Encrypt=yes;’:

-- Create the linked server:

EXEC sp_addlinkedserver
     @server     = 'LocalLinkedServername',
     @srvproduct = N'Any',
     @provider   = 'SQLNCLI',
     @datasrc    = '???.database.windows.net', -- Azure server name
     @location   = '',
     @provstr    = N'Encrypt=yes;',       -- <<--  Important!
     @catalog    = 'RemoteDatabaseName';  -- remote(Azure) database name
go

ADO.NET Connection strings

Add “ENCRYPT = true” to your connection string, or set the SqlConnectionStringBuilder property to True.

[Remember: don’t distribute passwords by sending as plaintext over the Internet, i.e. don’t email passwords! ]