client / server packets

[Richard Roche]

SQL 2005 - Access 2007 (project)
are the packets sent between client and server in plain text?
if so what are options to encrypt them.

Thanks

 

[Sylvain Lafontaine]

If you're not using a link over SSL, by default, the login will be encrypted
(using a self generated certificate) but the rest of the communication will
not be. However, you can configure the server to encrypt all of its
communication. To my knowledge, you cannot have a mixed environment where
some of the communications will be encrypted and the others not; so this is
why you cannot ask the communication to be encrypted from the client side.

 

[Richard Roche]

Thanks. I'm a developer and weak on server networking. Can SSL be used w/
SQL port 1433? What are the options you'd recommend so encryption? Group
Policy with ipsec?

 

[Paul Shapiro]

Look in SQL BooksOnLine at the index entries for "encryption". Rather than
going at the network configuration, you can tell SQL Server to encrypt the
connection itself. Instructions can be found at the index entry for
"encryption [SQL Server], Internet connections".

Here's part of what it says for the SQL Native Client:
SQL Server 2005 always encrypts network packets associated with logging in.
If no certificate has been provisioned on the server when it starts up, SQL
Server generates a self-signed certificate which is used to encrypt login
packets.

Applications may also request encryption of all network traffic by using
connection string keywords or connection properties. The keywords are
"Encrypt" for ODBC and OLE DB when using a provider string with
IDbInitialize::Initialize, or "Use Encryption for Data" for ADO and OLE DB
when using an initialization string with IDataInitialize. This may also be
configured by SQL Server Configuration Manager using the Force Protocol
Encryption option. By default, encryption of all network traffic for a
connection requires that a certificate be provisioned on the server.

To enable encryption to be used when a certificate has not been provisioned
on the server, SQL Server Configuration Manager can be used to set both the
Force Protocol Encryption and the Trust Server Certificate options. In this
case, encryption will use a self-signed server certificate without
validation if no verifiable certificate has been provisioned on the server.

Applications may also use the "TrustServerCertificate" keyword or its
associated connection attribute to guarantee that encryption takes place.
Application settings never reduce the level of security set by SQL Server
Client Configuration Manager, but may strengthen it. For example, if Force
Protocol Encryption is not set for the client, an application may request
encryption itself. To guarantee encryption even when a server certificate
has not been provisioned, an application may request encryption and
"TrustServerCertificate". However, if "TrustServerCertificate" is not
enabled in the client configuration, a provisioned server certificate is
still required.

 

[Sylvain Lafontaine]

I don't know, I'm not a dba; you should ask on a newsgroup about SQL-Server
and security such as microsoft.public.sqlserver.security what would be your
best option. Don't forget to describe your problem (nature of your security
needs, type of network, etc.).

 

[Sylvain Lafontaine]

I was curious and I made a quick check on the Internet: the following
reference say that even with SSL, SQL-Server will still be using the port
1433 (if it's the default port to be used, of course):

http://www.eggheadcafe.com/software/aspnet/32011928/encrypting-connections-to.aspx

Also, since MDAC 2.6, it seems that you can now invoke an encryption on a
per client basis instead of for everyone:

http://support.microsoft.com/kb/316898
http://support.microsoft.com/kb/811906

However, these articles don't say if you can use the "Encrypt=YES" for ODBC
or "Use Encryption for Data=True" for OLEDB even if there is no certificated
installed on the server.