Does SQL 2005 encryption on x64 work?

Are there any known issues with EncryptByKey/DecryptByKey on x64 machines?

I have a test script where I create a sample table and encrypt a column and later decrypt it. It works fine on my x86 box. When I run the *exact* same test script on an x64 server I'm getting unprintable characters back on the DecryptByKey. I cannont find anything I'm doing different between the two.

Has anyone seen anything like this before?

We have not seen this problems yet but I will take a look and attempt to repro this. Would it be possible to get a copy of the script you are using (obviously, please remove all private/sensitive info first)?

Thanks,

Sung

|||

Nevermind! I figured it out.

I was doing an INSERT INTO SELECT FROM... and the column I was selecting from, TaxID, had to be converted to Unicode first. So it ended up looking like this:

EncryptByKey(@.KeyGuid, CAST([TaxID] As NVARCHAR(9))) As 'TaxID'

On my x86 machine I had hard-coded the TaxID as N'123456789' and didn't realize at the time that the N was converting it to Unicode for me.

|||

Ah, ok, I was wondering if it had something to do with text encoding as well. Glad to hear it works!

Sung

Does Force Protocol Encryption on Server Require Certificate to be Installed on Client?

So I was able to enable the option to Force Protocol Encryption using
SQL. However, I was still able to connect to this instance of SQL
using the Query Analyzer tool from a machine without the certificate
(public key) installed.
This leads me to believe that the client connected to the server w/out
encrypting communications or that the client doesnt require a
certificate in order to connect (encrypted) to the instance of SQL now
configured to force protocol encryption. As a result, I have the
following question.
Once protocol encryption is enabled on the server using the SQL Server
Network Utility, must the certificate (public key) be installed on the
client in order to access SQL on the server (now configured to force
protocol encryption)?
What does it mean when I connect to the instance of SQL from a machine
w/out the certificate? How is this working? Are communications still
encrypted from client to server?
Thanks in advance for any help/direction.
If found a good article.
http://msdn.microsoft.com/library/de...SecNetHT19.asp
"Note that, in all cases, SQL Server sends its server authentication
certificate in the clear to the client at the beginning of the
communication sequence. This is part of the SSL protocol. Note that
this occurs even when neither the server nor the client requires
encryption."
nasteric@.yahoo.com (nasteric) wrote in message news:<e651d8ae.0406012052.5e1c2f41@.posting.google. com>...
> So I was able to enable the option to Force Protocol Encryption using
> SQL. However, I was still able to connect to this instance of SQL
> using the Query Analyzer tool from a machine without the certificate
> (public key) installed.
> This leads me to believe that the client connected to the server w/out
> encrypting communications or that the client doesnt require a
> certificate in order to connect (encrypted) to the instance of SQL now
> configured to force protocol encryption. As a result, I have the
> following question.
> Once protocol encryption is enabled on the server using the SQL Server
> Network Utility, must the certificate (public key) be installed on the
> client in order to access SQL on the server (now configured to force
> protocol encryption)?
> What does it mean when I connect to the instance of SQL from a machine
> w/out the certificate? How is this working? Are communications still
> encrypted from client to server?
> Thanks in advance for any help/direction.

Does Force Protocol Encryption on Server Require Certificate to be Installed on Client?

So I was able to enable the option to Force Protocol Encryption using
SQL. However, I was still able to connect to this instance of SQL
using the Query Analyzer tool from a machine without the certificate
(public key) installed.
This leads me to believe that the client connected to the server w/out
encrypting communications or that the client doesnt require a
certificate in order to connect (encrypted) to the instance of SQL now
configured to force protocol encryption. As a result, I have the
following question.
Once protocol encryption is enabled on the server using the SQL Server
Network Utility, must the certificate (public key) be installed on the
client in order to access SQL on the server (now configured to force
protocol encryption)?
What does it mean when I connect to the instance of SQL from a machine
w/out the certificate? How is this working? Are communications still
encrypted from client to server?
Thanks in advance for any help/direction.If found a good article.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetHT19.asp
"Note that, in all cases, SQL Server sends its server authentication
certificate in the clear to the client at the beginning of the
communication sequence. This is part of the SSL protocol. Note that
this occurs even when neither the server nor the client requires
encryption."
nasteric@.yahoo.com (nasteric) wrote in message news:<e651d8ae.0406012052.5e1c2f41@.posting.google.com>...
> So I was able to enable the option to Force Protocol Encryption using
> SQL. However, I was still able to connect to this instance of SQL
> using the Query Analyzer tool from a machine without the certificate
> (public key) installed.
> This leads me to believe that the client connected to the server w/out
> encrypting communications or that the client doesnt require a
> certificate in order to connect (encrypted) to the instance of SQL now
> configured to force protocol encryption. As a result, I have the
> following question.
> Once protocol encryption is enabled on the server using the SQL Server
> Network Utility, must the certificate (public key) be installed on the
> client in order to access SQL on the server (now configured to force
> protocol encryption)?
> What does it mean when I connect to the instance of SQL from a machine
> w/out the certificate? How is this working? Are communications still
> encrypted from client to server?
> Thanks in advance for any help/direction.

Does Force Protocol Encryption on Server Require Certificate to be Installed on Client

So I was able to enable the option to Force Protocol Encryption using
SQL. However, I was still able to connect to this instance of SQL
using the Query Analyzer tool from a machine without the certificate
(public key) installed.
This leads me to believe that the client connected to the server w/out
encrypting communications or that the client doesnt require a
certificate in order to connect (encrypted) to the instance of SQL now
configured to force protocol encryption. As a result, I have the
following question.
Once protocol encryption is enabled on the server using the SQL Server
Network Utility, must the certificate (public key) be installed on the
client in order to access SQL on the server (now configured to force
protocol encryption)?
What does it mean when I connect to the instance of SQL from a machine
w/out the certificate? How is this working? Are communications still
encrypted from client to server?
Thanks in advance for any help/direction.If you have a certificate on the Server and Force Protocol Encryption
turned on then the traffic is encrypted to and from all clients. The client
does not need a certificate.
Look at the following article:
HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft Management
Console
http://support.microsoft.com/?id=316898
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Previous post:
"Once protocol encryption is enabled on the server using the SQL Server
Network Utility, must the certificate (public key) be installed on the
client in order to access SQL on the server (now configured to force
protocol encryption)?
"
The client only requires that the Trusted Root is updated if you enable
protocol encryption from the client.
If it is enabled via Server Network Utility, then the client doesn't need
to Trust the same Root Authority.
Essentially, all you need is a valid certificate on the server.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||If found a good article.
NetHT19.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
NetHT19.asp
"Note that, in all cases, SQL Server sends its server authentication
certificate in the clear to the client at the beginning of the
communication sequence. This is part of the SSL protocol. Note that
this occurs even when neither the server nor the client requires
encryption."
nasteric@.yahoo.com (nasteric) wrote in message news:<e651d8ae.0406012052.5e1c2f41@.posting.go
ogle.com>...
> So I was able to enable the option to Force Protocol Encryption using
> SQL. However, I was still able to connect to this instance of SQL
> using the Query Analyzer tool from a machine without the certificate
> (public key) installed.
> This leads me to believe that the client connected to the server w/out
> encrypting communications or that the client doesnt require a
> certificate in order to connect (encrypted) to the instance of SQL now
> configured to force protocol encryption. As a result, I have the
> following question.
> Once protocol encryption is enabled on the server using the SQL Server
> Network Utility, must the certificate (public key) be installed on the
> client in order to access SQL on the server (now configured to force
> protocol encryption)?
> What does it mean when I connect to the instance of SQL from a machine
> w/out the certificate? How is this working? Are communications still
> encrypted from client to server?
> Thanks in advance for any help/direction.

Does Force Protocol Encryption on Server Require Certificate to be Installed on Client

So I was able to enable the option to Force Protocol Encryption using
SQL. However, I was still able to connect to this instance of SQL
using the Query Analyzer tool from a machine without the certificate
(public key) installed.
This leads me to believe that the client connected to the server w/out
encrypting communications or that the client doesnt require a
certificate in order to connect (encrypted) to the instance of SQL now
configured to force protocol encryption. As a result, I have the
following question.
Once protocol encryption is enabled on the server using the SQL Server
Network Utility, must the certificate (public key) be installed on the
client in order to access SQL on the server (now configured to force
protocol encryption)?
What does it mean when I connect to the instance of SQL from a machine
w/out the certificate? How is this working? Are communications still
encrypted from client to server?
Thanks in advance for any help/direction.If found a good article.
NetHT19.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
NetHT19.asp
"Note that, in all cases, SQL Server sends its server authentication
certificate in the clear to the client at the beginning of the
communication sequence. This is part of the SSL protocol. Note that
this occurs even when neither the server nor the client requires
encryption."
nasteric@.yahoo.com (nasteric) wrote in message news:<e651d8ae.0406012052.5e1c2f41@.posting.go
ogle.com>...
> So I was able to enable the option to Force Protocol Encryption using
> SQL. However, I was still able to connect to this instance of SQL
> using the Query Analyzer tool from a machine without the certificate
> (public key) installed.
> This leads me to believe that the client connected to the server w/out
> encrypting communications or that the client doesnt require a
> certificate in order to connect (encrypted) to the instance of SQL now
> configured to force protocol encryption. As a result, I have the
> following question.
> Once protocol encryption is enabled on the server using the SQL Server
> Network Utility, must the certificate (public key) be installed on the
> client in order to access SQL on the server (now configured to force
> protocol encryption)?
> What does it mean when I connect to the instance of SQL from a machine
> w/out the certificate? How is this working? Are communications still
> encrypted from client to server?
> Thanks in advance for any help/direction.