How do I enable encryption?

Here's the setup.
We have an internal certificate server. (This should be OK because all
of the clients will be inside our firewall.)
Both the client and the SQL Server computer have the CA chain for the
certificate server installed.
I have a server authentication certificate installed on the SQL
Server computer using the MS Enhanced Cryptographic Provider.
(And the certificate was issued to the fully qualified domain name that
I see when I type "ping devsql1".)
I have stopped and restarted SQL Server.
Nonetheless, when I try to force encryption, I get the error
"Encryption not supported on SQL Server."
How do I get SQL Server to use the certificate, or how do I find out
why SQL Server won't use the certificate? The lack of diagnostics is
extremely frustrating.
Mike Swaim swaim@.hal-pc.org at home | Quote: "Boingie"^4 Y,W & D
MD Anderson Dept. of Biostatistics & Applied Mathematics
mpswaim@.mdanderson.org or mswaim@.odin.mdacc.tmc.edu at work
ICBM: 29.763N -95.363W|Disclaimer: Yeah, like I speak for MD Anderson.If you enable protocol encryption on the clientside, then the client must
also have the Trusted Root Authority updated.
If you enable protocol encryption on the serverside this isn't required.
A quick test to validate that your certificate is valid is to force
protocol encryption on the server and then restart the MSSQLServer service.
If the server starts fine, then the cert is good. Otherwise, the server
won't start and we'll know that there is either a problem with the cert or
the account used to start SQL doesn't have access to the cert store.
If the server side option works, then remove the force protocol encryption
on the server, restart the MSSQLServer service, and enable it on the client
after updating the Trusted Root Authority on the client machine.
Let me know your results.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin McDonnell [MSFT] wrote:

> If you enable protocol encryption on the clientside, then the client
> must also have the Trusted Root Authority updated.
> If you enable protocol encryption on the serverside this isn't
> required.
My client has had its trusted root authority updated.

> A quick test to validate that your certificate is valid is to force
> protocol encryption on the server and then restart the MSSQLServer
> service. If the server starts fine, then the cert is good.
> Otherwise, the server won't start and we'll know that there is either
> a problem with the cert or the account used to start SQL doesn't have
> access to the cert store.
SQL Server won't start with force encryption enabled. SQL Server is
running as a custom domain account. What rights on the box does the
account need to have to access the cert store?
I'm assuming that the certificate authority is good because we have a
certificate from the same machine on our development web server, and
it's happy as a clam.
Mike Swaim swaim@.hal-pc.org at home | Quote: "Boingie"^4 Y,W & D
MD Anderson Dept. of Biostatistics & Applied Mathematics
mpswaim@.mdanderson.org or mswaim@.odin.mdacc.tmc.edu at work
ICBM: 29.763N -95.363W|Disclaimer: Yeah, like I speak for MD Anderson.|||Prev Post:
SQL Server won't start with force encryption enabled. SQL Server is
running as a custom domain account. What rights on the box does the
account need to have to access the cert store?
Reply:
The account that SQL Server is running under must have requested the cert.
Otherwise, the server will start and look in the local store, which only
admins have permission to, and the user store, which is probably empty.
So, what you can do is logon to the machine as the account that SQL service
is running with and request a new server certificate. Then stop n start
the mssqlserver service.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin McDonnell [MSFT] wrote:

> Reply:
> The account that SQL Server is running under must have requested the
> cert. Otherwise, the server will start and look in the local store,
> which only admins have permission to, and the user store, which is
> probably empty. So, what you can do is logon to the machine as the
> account that SQL service is running with and request a new server
> certificate. Then stop n start the mssqlserver service.
Woo hoo! That did it. Thanks.
Mike Swaim swaim@.hal-pc.org at home | Quote: "Boingie"^4 Y,W & D
MD Anderson Dept. of Biostatistics & Applied Mathematics
mpswaim@.mdanderson.org or mswaim@.odin.mdacc.tmc.edu at work
ICBM: 29.763N -95.363W|Disclaimer: Yeah, like I speak for MD Anderson.|||You're welcome!
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.