Cluster failing connections with SSL error message

We recently migrated our production environment from a win2k3/SQL 2000 EE cluster to a new 64 bit win2k3/ SQL 2005 SP1 Cluster.

Cluster works fine for a while (3-6 hours), then our logins start failing. We recieve the following error in the event log:

"The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications, depending on how the administrator has configured the server. See Books Online for information on this error message"

The only way to resume connectivity is to offline and online the sql service in the cluster.

The only reference to this error that I can find has something to do with a hotfix that might be available?

It's sad to say but we are experiencing the same issue.
Only we get this after a good 30 minutes...

Someone from Microsoft Spain is flying over here.

Very painful

|||

In working with microsoft PSS, we found the issue to be a permissions issue of all things.

Our service account did not have rights to lock pages in memory on either machines in the cluster. (even though is a domain administrator).

Once we gave it this right and restarted the SQL Server object in the cluster we have not had a similar failure since.

|||

This was unfortunately enabled already so it did not cause the problem here :-(

|||Hi Wesley,

Any joy with this? I'm currently going throught the same thing, and also have locked pages enabled for my SQL service account. I'm currently going through MS Support, but seem to be going around the houses :o(

Thanks

Stu|||Any luck, we have a similar problem.
We noticed that memory usage grows slowly beyond physical memory (32G) then connection failiurs start and server becomes inoperable.
Workaround solution is to reduce max mem in the server, down to 18G from (25).
This causes the server and OS to release memory. After the system stabilized we increased the maxmem in the server back to its original settng. Lock pages in memory is set.

Hope this helps.

This is a workaround, so we don't have to bounce the server, we are still looking for the cause.
esk.

Setup:2 node Cluster 4*dual 64bit AMD, 32Gb, SQL 2005 Enterprise SP0.|||

Hi,

Apparently we have hit a bug where the usercache size becomes too large and for every execution of a statement SQL Server has to check this store.
This store is not cleaned and keep growing too the point where lookups become blocked with spinlocks. If you want I can give you the commands to check this.

This will be fixed in SP2 because of the major impact a fix would have. The workaround is adding the users to the sysadmin group or clearing the cache manually with DBCC FREESYSTEMCACHE.

HTH

Kind regards,
Wesley

|||Thanks for the replies !

Wes, if you could give the command to check the spinlocks that would be great.

many thanks

Stu|||

Stu,

It's actually the query for checking the cache size.

SELECT * FROM sys.dm_os_memory_clerks
WHERE type = 'USERSTORE_TOKENPERM'
We have seen problems as of 150MB in our environment but this probably all depends on the load and types of queries. Add up the single pages and multipages to determine the size.

HTH
Wesley

|||Thanks Wesley!

After much investigation it turned out the problem was the iLO Management Channel Interface Driver (Cpqcidrv.sys) from Hewlett-Packard. Apparently this

is known to cause this issue on x64 editions of SQL Server 2005. The driver

causes the SQL Server 2005 64-bit working set to be trimmed!


We do not use ILO on our HP Servers, so I disable it in device manager, and so far so good (fingers crossed)!!!

Thanks

Stu|||

Great news!

There seems to be a hotfix for Windows 2003 concerning working set trims too.
Apparently working sets get trimmed when you use Terminal Services on the server... so beware ;-)

|||

If possible could you share the hotfix number? Thanks in advance,

Drew

|||

Hey Wesley. Can you please elaborate more on this 'working sets get trimmed' and beware. and the hotfix available? Thank you very much. I've had problems with this also.

|||

I am also have a problem with this issue. I am going to try disabling the ILO. The terminal service hotfix seems to be this one:

http://support.microsoft.com/kb/905865/en-us

sqlsql

Cluster failing connections with SSL error message

We recently migrated our production environment from a win2k3/SQL 2000 EE cluster to a new 64 bit win2k3/ SQL 2005 SP1 Cluster.

Cluster works fine for a while (3-6 hours), then our logins start failing. We recieve the following error in the event log:

"The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications, depending on how the administrator has configured the server. See Books Online for information on this error message"

The only way to resume connectivity is to offline and online the sql service in the cluster.

The only reference to this error that I can find has something to do with a hotfix that might be available?

It's sad to say but we are experiencing the same issue.
Only we get this after a good 30 minutes...

Someone from Microsoft Spain is flying over here.

Very painful

|||

In working with microsoft PSS, we found the issue to be a permissions issue of all things.

Our service account did not have rights to lock pages in memory on either machines in the cluster. (even though is a domain administrator).

Once we gave it this right and restarted the SQL Server object in the cluster we have not had a similar failure since.

|||

This was unfortunately enabled already so it did not cause the problem here :-(

|||Hi Wesley,

Any joy with this? I'm currently going throught the same thing, and also have locked pages enabled for my SQL service account. I'm currently going through MS Support, but seem to be going around the houses :o(

Thanks

Stu|||Any luck, we have a similar problem.
We noticed that memory usage grows slowly beyond physical memory (32G) then connection failiurs start and server becomes inoperable.
Workaround solution is to reduce max mem in the server, down to 18G from (25).
This causes the server and OS to release memory. After the system stabilized we increased the maxmem in the server back to its original settng. Lock pages in memory is set.

Hope this helps.

This is a workaround, so we don't have to bounce the server, we are still looking for the cause.
esk.

Setup:2 node Cluster 4*dual 64bit AMD, 32Gb, SQL 2005 Enterprise SP0.|||

Hi,

Apparently we have hit a bug where the usercache size becomes too large and for every execution of a statement SQL Server has to check this store.
This store is not cleaned and keep growing too the point where lookups become blocked with spinlocks. If you want I can give you the commands to check this.

This will be fixed in SP2 because of the major impact a fix would have. The workaround is adding the users to the sysadmin group or clearing the cache manually with DBCC FREESYSTEMCACHE.

HTH

Kind regards,
Wesley

|||Thanks for the replies !

Wes, if you could give the command to check the spinlocks that would be great.

many thanks

Stu|||

Stu,

It's actually the query for checking the cache size.

SELECT * FROM sys.dm_os_memory_clerks
WHERE type = 'USERSTORE_TOKENPERM'
We have seen problems as of 150MB in our environment but this probably all depends on the load and types of queries. Add up the single pages and multipages to determine the size.

HTH
Wesley

|||Thanks Wesley!

After much investigation it turned out the problem was the iLO Management Channel Interface Driver (Cpqcidrv.sys) from Hewlett-Packard. Apparently this

is known to cause this issue on x64 editions of SQL Server 2005. The driver

causes the SQL Server 2005 64-bit working set to be trimmed!


We do not use ILO on our HP Servers, so I disable it in device manager, and so far so good (fingers crossed)!!!

Thanks

Stu|||

Great news!

There seems to be a hotfix for Windows 2003 concerning working set trims too.
Apparently working sets get trimmed when you use Terminal Services on the server... so beware ;-)

|||

If possible could you share the hotfix number? Thanks in advance,

Drew

|||

Hey Wesley. Can you please elaborate more on this 'working sets get trimmed' and beware. and the hotfix available? Thank you very much. I've had problems with this also.

|||

I am also have a problem with this issue. I am going to try disabling the ILO. The terminal service hotfix seems to be this one:

http://support.microsoft.com/kb/905865/en-us

Cluster failing connections with SSL error message

We recently migrated our production environment from a win2k3/SQL 2000 EE cluster to a new 64 bit win2k3/ SQL 2005 SP1 Cluster.

Cluster works fine for a while (3-6 hours), then our logins start failing. We recieve the following error in the event log:

"The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications, depending on how the administrator has configured the server. See Books Online for information on this error message"

The only way to resume connectivity is to offline and online the sql service in the cluster.

The only reference to this error that I can find has something to do with a hotfix that might be available?

It's sad to say but we are experiencing the same issue.
Only we get this after a good 30 minutes...

Someone from Microsoft Spain is flying over here.

Very painful

|||

In working with microsoft PSS, we found the issue to be a permissions issue of all things.

Our service account did not have rights to lock pages in memory on either machines in the cluster. (even though is a domain administrator).

Once we gave it this right and restarted the SQL Server object in the cluster we have not had a similar failure since.

|||

This was unfortunately enabled already so it did not cause the problem here :-(

|||Hi Wesley,

Any joy with this? I'm currently going throught the same thing, and also have locked pages enabled for my SQL service account. I'm currently going through MS Support, but seem to be going around the houses :o(

Thanks

Stu|||Any luck, we have a similar problem.
We noticed that memory usage grows slowly beyond physical memory (32G) then connection failiurs start and server becomes inoperable.
Workaround solution is to reduce max mem in the server, down to 18G from (25).
This causes the server and OS to release memory. After the system stabilized we increased the maxmem in the server back to its original settng. Lock pages in memory is set.

Hope this helps.

This is a workaround, so we don't have to bounce the server, we are still looking for the cause.
esk.

Setup:2 node Cluster 4*dual 64bit AMD, 32Gb, SQL 2005 Enterprise SP0.|||

Hi,

Apparently we have hit a bug where the usercache size becomes too large and for every execution of a statement SQL Server has to check this store.
This store is not cleaned and keep growing too the point where lookups become blocked with spinlocks. If you want I can give you the commands to check this.

This will be fixed in SP2 because of the major impact a fix would have. The workaround is adding the users to the sysadmin group or clearing the cache manually with DBCC FREESYSTEMCACHE.

HTH

Kind regards,
Wesley

|||Thanks for the replies !

Wes, if you could give the command to check the spinlocks that would be great.

many thanks

Stu|||

Stu,

It's actually the query for checking the cache size.

SELECT * FROM sys.dm_os_memory_clerks
WHERE type = 'USERSTORE_TOKENPERM'
We have seen problems as of 150MB in our environment but this probably all depends on the load and types of queries. Add up the single pages and multipages to determine the size.

HTH
Wesley

|||Thanks Wesley!

After much investigation it turned out the problem was the iLO Management Channel Interface Driver (Cpqcidrv.sys) from Hewlett-Packard. Apparently this

is known to cause this issue on x64 editions of SQL Server 2005. The driver

causes the SQL Server 2005 64-bit working set to be trimmed!


We do not use ILO on our HP Servers, so I disable it in device manager, and so far so good (fingers crossed)!!!

Thanks

Stu|||

Great news!

There seems to be a hotfix for Windows 2003 concerning working set trims too.
Apparently working sets get trimmed when you use Terminal Services on the server... so beware ;-)

|||

If possible could you share the hotfix number? Thanks in advance,

Drew

|||

Hey Wesley. Can you please elaborate more on this 'working sets get trimmed' and beware. and the hotfix available? Thank you very much. I've had problems with this also.

|||

I am also have a problem with this issue. I am going to try disabling the ILO. The terminal service hotfix seems to be this one:

http://support.microsoft.com/kb/905865/en-us

Cluster failing connections with SSL error message

We recently migrated our production environment from a win2k3/SQL 2000 EE cluster to a new 64 bit win2k3/ SQL 2005 SP1 Cluster.

Cluster works fine for a while (3-6 hours), then our logins start failing. We recieve the following error in the event log:

"The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications, depending on how the administrator has configured the server. See Books Online for information on this error message"

The only way to resume connectivity is to offline and online the sql service in the cluster.

The only reference to this error that I can find has something to do with a hotfix that might be available?

It's sad to say but we are experiencing the same issue.
Only we get this after a good 30 minutes...

Someone from Microsoft Spain is flying over here.

Very painful

|||

In working with microsoft PSS, we found the issue to be a permissions issue of all things.

Our service account did not have rights to lock pages in memory on either machines in the cluster. (even though is a domain administrator).

Once we gave it this right and restarted the SQL Server object in the cluster we have not had a similar failure since.

|||

This was unfortunately enabled already so it did not cause the problem here :-(

|||Hi Wesley,

Any joy with this? I'm currently going throught the same thing, and also have locked pages enabled for my SQL service account. I'm currently going through MS Support, but seem to be going around the houses :o(

Thanks

Stu|||Any luck, we have a similar problem.
We noticed that memory usage grows slowly beyond physical memory (32G) then connection failiurs start and server becomes inoperable.
Workaround solution is to reduce max mem in the server, down to 18G from (25).
This causes the server and OS to release memory. After the system stabilized we increased the maxmem in the server back to its original settng. Lock pages in memory is set.

Hope this helps.

This is a workaround, so we don't have to bounce the server, we are still looking for the cause.
esk.

Setup:2 node Cluster 4*dual 64bit AMD, 32Gb, SQL 2005 Enterprise SP0.|||

Hi,

Apparently we have hit a bug where the usercache size becomes too large and for every execution of a statement SQL Server has to check this store.
This store is not cleaned and keep growing too the point where lookups become blocked with spinlocks. If you want I can give you the commands to check this.

This will be fixed in SP2 because of the major impact a fix would have. The workaround is adding the users to the sysadmin group or clearing the cache manually with DBCC FREESYSTEMCACHE.

HTH

Kind regards,
Wesley

|||Thanks for the replies !

Wes, if you could give the command to check the spinlocks that would be great.

many thanks

Stu|||

Stu,

It's actually the query for checking the cache size.

SELECT * FROM sys.dm_os_memory_clerks
WHERE type = 'USERSTORE_TOKENPERM'
We have seen problems as of 150MB in our environment but this probably all depends on the load and types of queries. Add up the single pages and multipages to determine the size.

HTH
Wesley

|||Thanks Wesley!

After much investigation it turned out the problem was the iLO Management Channel Interface Driver (Cpqcidrv.sys) from Hewlett-Packard. Apparently this

is known to cause this issue on x64 editions of SQL Server 2005. The driver

causes the SQL Server 2005 64-bit working set to be trimmed!


We do not use ILO on our HP Servers, so I disable it in device manager, and so far so good (fingers crossed)!!!

Thanks

Stu|||

Great news!

There seems to be a hotfix for Windows 2003 concerning working set trims too.
Apparently working sets get trimmed when you use Terminal Services on the server... so beware ;-)

|||

If possible could you share the hotfix number? Thanks in advance,

Drew

|||

Hey Wesley. Can you please elaborate more on this 'working sets get trimmed' and beware. and the hotfix available? Thank you very much. I've had problems with this also.

|||

I am also have a problem with this issue. I am going to try disabling the ILO. The terminal service hotfix seems to be this one:

http://support.microsoft.com/kb/905865/en-us

client-requested SSL encryption errors

I did not get any useful response the last time I posted this, so now I'm
posting a more detailed version of the question.
We are having difficulty getting client-requested SSL encryption to work
with SQL Server 2000 Enterprise SP4.
Using "Force All Clients to Use SSL" is not an option for us. We need to be
able to have certain clients (extranet)use encryption without forcing other
clients (intranet) to also use encryption. Hence, we need to know how to
make "Force Protocol Encryption" work from the Client Network Utility with
SQL Query Analyzer.
Ultimately, our goal is to enable encryption in certain connection requests
from custom client applications we are writing. However, we will be happy
for now if we can at least get it working from a standard MS tool as
described in the SQL Server documentation and KB articles.
Has anyone else managed to make client-requested encryption work without
using a commercial CA? For that matter, has anyone suceeded in making it
work *with* a commercial CA?
When I connect using SQL Query Analyzer without Force Protocol Encryption in
the Client Network Utility, everything works fine. When I select Force
Protocol Encryption, then I get the following error:
====
unable to connect to server
server: msg 18, level 16, state 1
[microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error
====
The server is running Windows Server 2003 Enterprise SP1. As mentioned
earlier, MSSQLSERVER version is SQL Server 2000 Enterprise SP4.
The Server Authentication certificate is installed correctly on the SQL
Server 2000 machine. It was generated by Microsoft Certificate Services
configured as a stand-alone root CA. The certificate chain is OK according
to MMC snap-in and works fine with no warnings for HTTPS connections to IIS,
so I don't see how the certificate could be malformed. There definitely is
only one certificate installed on the server (at least according to the MMC
snap-in for Certificates). The Root CA chain is installed on the client and
is OK according to MMC. This seems to be validated by the fact that IE
doesn't give any warnings when making an HTTPS connection to the server
(i.e., it recognizes the certificate chain as a trusted source).
I've read every KB article I can find on the subject, followed all the
instructions with meticulous care, and reinstalled everything from scratch
twice already (including the CA, thus generating a new root certificate and
new server authentication certificate). The client still fails to connect
whenever I force client encryption (it's not feasible for us to set force
encryption on the server).
I've even tried creating various aliases for the server in the Client
Network Utility, as suggested in one KB article, but that doesn't seem to
help either.
Perhaps I'm overlooking something really obvious, but I'm seriously
beginning to doubt whether SQL Server really supports client-initiated SSL
connections at all. Has anyone else gotten this to work? If so, what was the
trick to making it work?
Any suggestions would be greatly appreciated.
Hello Aubrey,
I don't think you need a commercial certfiicate to do this.
If you want to enable Force Protocol Encryption on the client, you must
have a certificate on the server and the client must have the Trusted Root
Authority updated to trust the server certificate. You can install your own
CA and get certficate from it on the server, and install root CA
certificate on the client.
You may have reviewed the following articles but the steps to enable SSL
from client are verified to work properly.
316898 How to enable SSL encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
316779 PRB: Clients with Force Protocol Encryption Set On May Fail to
Connect
http://support.microsoft.com/?id=316779
276553 How to enable SSL encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
Also, to make sure the certificate installed on the SQL server is correct,
we suggest that you enable "Force Protocol Encryption" temporarily and
disable "Force Protocol Encryption" on client to test the situation. If it
works under this situation, the server certificate itself has no issues.
Note: EVEN IF YOU ARE ENABLING FORCE PROTOCOL ENCRYPTION ON THE CLIENT SIDE
ONLY, YOU STILL NEED TO RESTART SQL SERVER FOR THE CERTIFICATE TO BECOME
EFFECTIVE AND USED BY SQL SERVER.
If "Force Protocol Encryption" on server does not work, please check the
certificate property to make sure it is for FQDN for the SQL server.
839617 BUG: You cannot connect to an instance of SQL Server on a server
http://support.microsoft.com/?id=839617
You may want to check if the issue occurs on different client computers to
isolate the issue.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Aubrey McAuley" <winaix@.nospam.nospam>
| Subject: client-requested SSL encryption errors
| Date: Wed, 27 Jul 2005 16:10:49 -0500
| Lines: 65
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#Gi#t#ukFHA.2852@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: rrcs-67-79-5-147.sw.biz.rr.com 67.79.5.147
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:64999
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I did not get any useful response the last time I posted this, so now I'm
| posting a more detailed version of the question.
|
| We are having difficulty getting client-requested SSL encryption to work
| with SQL Server 2000 Enterprise SP4.
|
| Using "Force All Clients to Use SSL" is not an option for us. We need to
be
| able to have certain clients (extranet)use encryption without forcing
other
| clients (intranet) to also use encryption. Hence, we need to know how to
| make "Force Protocol Encryption" work from the Client Network Utility
with
| SQL Query Analyzer.
|
| Ultimately, our goal is to enable encryption in certain connection
requests
| from custom client applications we are writing. However, we will be happy
| for now if we can at least get it working from a standard MS tool as
| described in the SQL Server documentation and KB articles.
|
| Has anyone else managed to make client-requested encryption work without
| using a commercial CA? For that matter, has anyone suceeded in making it
| work *with* a commercial CA?
|
| When I connect using SQL Query Analyzer without Force Protocol Encryption
in
| the Client Network Utility, everything works fine. When I select Force
| Protocol Encryption, then I get the following error:
|
| ====
| unable to connect to server
| server: msg 18, level 16, state 1
| [microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error
|
| ====
|
| The server is running Windows Server 2003 Enterprise SP1. As mentioned
| earlier, MSSQLSERVER version is SQL Server 2000 Enterprise SP4.
|
| The Server Authentication certificate is installed correctly on the SQL
| Server 2000 machine. It was generated by Microsoft Certificate Services
| configured as a stand-alone root CA. The certificate chain is OK
according
| to MMC snap-in and works fine with no warnings for HTTPS connections to
IIS,
| so I don't see how the certificate could be malformed. There definitely
is
| only one certificate installed on the server (at least according to the
MMC
| snap-in for Certificates). The Root CA chain is installed on the client
and
| is OK according to MMC. This seems to be validated by the fact that IE
| doesn't give any warnings when making an HTTPS connection to the server
| (i.e., it recognizes the certificate chain as a trusted source).
|
| I've read every KB article I can find on the subject, followed all the
| instructions with meticulous care, and reinstalled everything from
scratch
| twice already (including the CA, thus generating a new root certificate
and
| new server authentication certificate). The client still fails to connect
| whenever I force client encryption (it's not feasible for us to set force
| encryption on the server).
|
| I've even tried creating various aliases for the server in the Client
| Network Utility, as suggested in one KB article, but that doesn't seem to
| help either.
|
| Perhaps I'm overlooking something really obvious, but I'm seriously
| beginning to doubt whether SQL Server really supports client-initiated
SSL
| connections at all. Has anyone else gotten this to work? If so, what was
the
| trick to making it work?
|
| Any suggestions would be greatly appreciated.
|
|
|

client-requested SSL encryption errors

I did not get any useful response the last time I posted this, so now I'm
posting a more detailed version of the question.
We are having difficulty getting client-requested SSL encryption to work
with SQL Server 2000 Enterprise SP4.
Using "Force All Clients to Use SSL" is not an option for us. We need to be
able to have certain clients (extranet)use encryption without forcing other
clients (intranet) to also use encryption. Hence, we need to know how to
make "Force Protocol Encryption" work from the Client Network Utility with
SQL Query Analyzer.
Ultimately, our goal is to enable encryption in certain connection requests
from custom client applications we are writing. However, we will be happy
for now if we can at least get it working from a standard MS tool as
described in the SQL Server documentation and KB articles.
Has anyone else managed to make client-requested encryption work without
using a commercial CA? For that matter, has anyone suceeded in making it
work *with* a commercial CA?
When I connect using SQL Query Analyzer without Force Protocol Encryption in
the Client Network Utility, everything works fine. When I select Force
Protocol Encryption, then I get the following error:
==== unable to connect to server
server: msg 18, level 16, state 1
[microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error
====
The server is running Windows Server 2003 Enterprise SP1. As mentioned
earlier, MSSQLSERVER version is SQL Server 2000 Enterprise SP4.
The Server Authentication certificate is installed correctly on the SQL
Server 2000 machine. It was generated by Microsoft Certificate Services
configured as a stand-alone root CA. The certificate chain is OK according
to MMC snap-in and works fine with no warnings for HTTPS connections to IIS,
so I don't see how the certificate could be malformed. There definitely is
only one certificate installed on the server (at least according to the MMC
snap-in for Certificates). The Root CA chain is installed on the client and
is OK according to MMC. This seems to be validated by the fact that IE
doesn't give any warnings when making an HTTPS connection to the server
(i.e., it recognizes the certificate chain as a trusted source).
I've read every KB article I can find on the subject, followed all the
instructions with meticulous care, and reinstalled everything from scratch
twice already (including the CA, thus generating a new root certificate and
new server authentication certificate). The client still fails to connect
whenever I force client encryption (it's not feasible for us to set force
encryption on the server).
I've even tried creating various aliases for the server in the Client
Network Utility, as suggested in one KB article, but that doesn't seem to
help either.
Perhaps I'm overlooking something really obvious, but I'm seriously
beginning to doubt whether SQL Server really supports client-initiated SSL
connections at all. Has anyone else gotten this to work? If so, what was the
trick to making it work?
Any suggestions would be greatly appreciated.Hello Aubrey,
I don't think you need a commercial certfiicate to do this.
If you want to enable Force Protocol Encryption on the client, you must
have a certificate on the server and the client must have the Trusted Root
Authority updated to trust the server certificate. You can install your own
CA and get certficate from it on the server, and install root CA
certificate on the client.
You may have reviewed the following articles but the steps to enable SSL
from client are verified to work properly.
316898 How to enable SSL encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
316779 PRB: Clients with Force Protocol Encryption Set On May Fail to
Connect
http://support.microsoft.com/?id=316779
276553 How to enable SSL encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
Also, to make sure the certificate installed on the SQL server is correct,
we suggest that you enable "Force Protocol Encryption" temporarily and
disable "Force Protocol Encryption" on client to test the situation. If it
works under this situation, the server certificate itself has no issues.
Note: EVEN IF YOU ARE ENABLING FORCE PROTOCOL ENCRYPTION ON THE CLIENT SIDE
ONLY, YOU STILL NEED TO RESTART SQL SERVER FOR THE CERTIFICATE TO BECOME
EFFECTIVE AND USED BY SQL SERVER.
If "Force Protocol Encryption" on server does not work, please check the
certificate property to make sure it is for FQDN for the SQL server.
839617 BUG: You cannot connect to an instance of SQL Server on a server
http://support.microsoft.com/?id=839617
You may want to check if the issue occurs on different client computers to
isolate the issue.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Aubrey McAuley" <winaix@.nospam.nospam>
| Subject: client-requested SSL encryption errors
| Date: Wed, 27 Jul 2005 16:10:49 -0500
| Lines: 65
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#Gi#t#ukFHA.2852@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: rrcs-67-79-5-147.sw.biz.rr.com 67.79.5.147
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:64999
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I did not get any useful response the last time I posted this, so now I'm
| posting a more detailed version of the question.
|
| We are having difficulty getting client-requested SSL encryption to work
| with SQL Server 2000 Enterprise SP4.
|
| Using "Force All Clients to Use SSL" is not an option for us. We need to
be
| able to have certain clients (extranet)use encryption without forcing
other
| clients (intranet) to also use encryption. Hence, we need to know how to
| make "Force Protocol Encryption" work from the Client Network Utility
with
| SQL Query Analyzer.
|
| Ultimately, our goal is to enable encryption in certain connection
requests
| from custom client applications we are writing. However, we will be happy
| for now if we can at least get it working from a standard MS tool as
| described in the SQL Server documentation and KB articles.
|
| Has anyone else managed to make client-requested encryption work without
| using a commercial CA? For that matter, has anyone suceeded in making it
| work *with* a commercial CA?
|
| When I connect using SQL Query Analyzer without Force Protocol Encryption
in
| the Client Network Utility, everything works fine. When I select Force
| Protocol Encryption, then I get the following error:
|
| ====| unable to connect to server
| server: msg 18, level 16, state 1
| [microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error
|
| ====|
| The server is running Windows Server 2003 Enterprise SP1. As mentioned
| earlier, MSSQLSERVER version is SQL Server 2000 Enterprise SP4.
|
| The Server Authentication certificate is installed correctly on the SQL
| Server 2000 machine. It was generated by Microsoft Certificate Services
| configured as a stand-alone root CA. The certificate chain is OK
according
| to MMC snap-in and works fine with no warnings for HTTPS connections to
IIS,
| so I don't see how the certificate could be malformed. There definitely
is
| only one certificate installed on the server (at least according to the
MMC
| snap-in for Certificates). The Root CA chain is installed on the client
and
| is OK according to MMC. This seems to be validated by the fact that IE
| doesn't give any warnings when making an HTTPS connection to the server
| (i.e., it recognizes the certificate chain as a trusted source).
|
| I've read every KB article I can find on the subject, followed all the
| instructions with meticulous care, and reinstalled everything from
scratch
| twice already (including the CA, thus generating a new root certificate
and
| new server authentication certificate). The client still fails to connect
| whenever I force client encryption (it's not feasible for us to set force
| encryption on the server).
|
| I've even tried creating various aliases for the server in the Client
| Network Utility, as suggested in one KB article, but that doesn't seem to
| help either.
|
| Perhaps I'm overlooking something really obvious, but I'm seriously
| beginning to doubt whether SQL Server really supports client-initiated
SSL
| connections at all. Has anyone else gotten this to work? If so, what was
the
| trick to making it work?
|
| Any suggestions would be greatly appreciated.
|
|
|

client-requested SSL encryption errors

I did not get any useful response the last time I posted this, so now I'm
posting a more detailed version of the question.
We are having difficulty getting client-requested SSL encryption to work
with SQL Server 2000 Enterprise SP4.
Using "Force All Clients to Use SSL" is not an option for us. We need to be
able to have certain clients (extranet)use encryption without forcing other
clients (intranet) to also use encryption. Hence, we need to know how to
make "Force Protocol Encryption" work from the Client Network Utility with
SQL Query Analyzer.
Ultimately, our goal is to enable encryption in certain connection requests
from custom client applications we are writing. However, we will be happy
for now if we can at least get it working from a standard MS tool as
described in the SQL Server documentation and KB articles.
Has anyone else managed to make client-requested encryption work without
using a commercial CA? For that matter, has anyone suceeded in making it
work *with* a commercial CA?
When I connect using SQL Query Analyzer without Force Protocol Encryption in
the Client Network Utility, everything works fine. When I select Force
Protocol Encryption, then I get the following error:
====
unable to connect to server
server: msg 18, level 16, state 1
[microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error
====
The server is running Windows Server 2003 Enterprise SP1. As mentioned
earlier, MSSQLSERVER version is SQL Server 2000 Enterprise SP4.
The Server Authentication certificate is installed correctly on the SQL
Server 2000 machine. It was generated by Microsoft Certificate Services
configured as a stand-alone root CA. The certificate chain is OK according
to MMC snap-in and works fine with no warnings for HTTPS connections to IIS,
so I don't see how the certificate could be malformed. There definitely is
only one certificate installed on the server (at least according to the MMC
snap-in for Certificates). The Root CA chain is installed on the client and
is OK according to MMC. This seems to be validated by the fact that IE
doesn't give any warnings when making an HTTPS connection to the server
(i.e., it recognizes the certificate chain as a trusted source).
I've read every KB article I can find on the subject, followed all the
instructions with meticulous care, and reinstalled everything from scratch
twice already (including the CA, thus generating a new root certificate and
new server authentication certificate). The client still fails to connect
whenever I force client encryption (it's not feasible for us to set force
encryption on the server).
I've even tried creating various aliases for the server in the Client
Network Utility, as suggested in one KB article, but that doesn't seem to
help either.
Perhaps I'm overlooking something really obvious, but I'm seriously
beginning to doubt whether SQL Server really supports client-initiated SSL
connections at all. Has anyone else gotten this to work? If so, what was the
trick to making it work?
Any suggestions would be greatly appreciated.Hello Aubrey,
I don't think you need a commercial certfiicate to do this.
If you want to enable Force Protocol Encryption on the client, you must
have a certificate on the server and the client must have the Trusted Root
Authority updated to trust the server certificate. You can install your own
CA and get certficate from it on the server, and install root CA
certificate on the client.
You may have reviewed the following articles but the steps to enable SSL
from client are verified to work properly.
316898 How to enable SSL encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
316779 PRB: Clients with Force Protocol Encryption Set On May Fail to
Connect
http://support.microsoft.com/?id=316779
276553 How to enable SSL encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
Also, to make sure the certificate installed on the SQL server is correct,
we suggest that you enable "Force Protocol Encryption" temporarily and
disable "Force Protocol Encryption" on client to test the situation. If it
works under this situation, the server certificate itself has no issues.
Note: EVEN IF YOU ARE ENABLING FORCE PROTOCOL ENCRYPTION ON THE CLIENT SIDE
ONLY, YOU STILL NEED TO RESTART SQL SERVER FOR THE CERTIFICATE TO BECOME
EFFECTIVE AND USED BY SQL SERVER.
If "Force Protocol Encryption" on server does not work, please check the
certificate property to make sure it is for FQDN for the SQL server.
839617 BUG: You cannot connect to an instance of SQL Server on a server
http://support.microsoft.com/?id=839617
You may want to check if the issue occurs on different client computers to
isolate the issue.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Aubrey McAuley" <winaix@.nospam.nospam>
| Subject: client-requested SSL encryption errors
| Date: Wed, 27 Jul 2005 16:10:49 -0500
| Lines: 65
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#Gi#t#ukFHA.2852@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: rrcs-67-79-5-147.sw.biz.rr.com 67.79.5.147
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:64999
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I did not get any useful response the last time I posted this, so now I'm
| posting a more detailed version of the question.
|
| We are having difficulty getting client-requested SSL encryption to work
| with SQL Server 2000 Enterprise SP4.
|
| Using "Force All Clients to Use SSL" is not an option for us. We need to
be
| able to have certain clients (extranet)use encryption without forcing
other
| clients (intranet) to also use encryption. Hence, we need to know how to
| make "Force Protocol Encryption" work from the Client Network Utility
with
| SQL Query Analyzer.
|
| Ultimately, our goal is to enable encryption in certain connection
requests
| from custom client applications we are writing. However, we will be happy
| for now if we can at least get it working from a standard MS tool as
| described in the SQL Server documentation and KB articles.
|
| Has anyone else managed to make client-requested encryption work without
| using a commercial CA? For that matter, has anyone suceeded in making it
| work *with* a commercial CA?
|
| When I connect using SQL Query Analyzer without Force Protocol Encryption
in
| the Client Network Utility, everything works fine. When I select Force
| Protocol Encryption, then I get the following error:
|
| ====
| unable to connect to server
| server: msg 18, level 16, state 1
| [microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security erro
r
|
| ====
|
| The server is running Windows Server 2003 Enterprise SP1. As mentioned
| earlier, MSSQLSERVER version is SQL Server 2000 Enterprise SP4.
|
| The Server Authentication certificate is installed correctly on the SQL
| Server 2000 machine. It was generated by Microsoft Certificate Services
| configured as a stand-alone root CA. The certificate chain is OK
according
| to MMC snap-in and works fine with no warnings for HTTPS connections to
IIS,
| so I don't see how the certificate could be malformed. There definitely
is
| only one certificate installed on the server (at least according to the
MMC
| snap-in for Certificates). The Root CA chain is installed on the client
and
| is OK according to MMC. This seems to be validated by the fact that IE
| doesn't give any warnings when making an HTTPS connection to the server
| (i.e., it recognizes the certificate chain as a trusted source).
|
| I've read every KB article I can find on the subject, followed all the
| instructions with meticulous care, and reinstalled everything from
scratch
| twice already (including the CA, thus generating a new root certificate
and
| new server authentication certificate). The client still fails to connect
| whenever I force client encryption (it's not feasible for us to set force
| encryption on the server).
|
| I've even tried creating various aliases for the server in the Client
| Network Utility, as suggested in one KB article, but that doesn't seem to
| help either.
|
| Perhaps I'm overlooking something really obvious, but I'm seriously
| beginning to doubt whether SQL Server really supports client-initiated
SSL
| connections at all. Has anyone else gotten this to work? If so, what was
the
| trick to making it work?
|
| Any suggestions would be greatly appreciated.
|
|
|

Client must have root CA certificate?

The following get quoted from SQL book on-line.
"SSL encryption works only with instances of SQL Server 2000 running on a co
mputer that has been assigned a certificate from a public certification auth
ority. The computer on which the application is running must also have a roo
t CA certificate from the s
ame authority."
My question is that my client application run on different host from SQL ser
ver and don't have the SQL server root CA
certificated installed on client host. How my client can talk to the SQL ser
ver and packet is encryped?Install the CA's root certificate on the client.
Cheers
Ken
"Ming Cheng" <anonymous@.discussions.microsoft.com> wrote in message
news:C11E32E7-54C3-446F-9697-4361C7605812@.microsoft.com...
: The following get quoted from SQL book on-line.
: "SSL encryption works only with instances of SQL Server 2000 running on a
computer that has been assigned a certificate from a public certification
authority. The computer on which the application is running must also have a
root CA certificate from the same authority."
:
: My question is that my client application run on different host from SQL
server and don't have the SQL server root CA
: certificated installed on client host. How my client can talk to the SQL
server and packet is encryped?
:
:

Client Certificates with SQL Mobile replication

I am currently trying to replicate a SQL Mobile 2005 database with a SQL Server 2005 database through web synchronization using SSL Server AND Client Certificates. On IIS, with "Require Client Certificates" unchecked, I can replicate fine. Once I turn it on, I get a message from replication saying "A Secure Socket Layer connection is required to access this site". I have installed a client certificate in IE, and can access the https://servername/Ojt/sqlcesa30.dll site (I tried removing the client certificate, and I was denied access, then reinstalled it and it worked - so I think that part is working). Does anyone have any experience with this? My production operating environment requires client-side certificates.

Can you make sure you have https:// in your client application and not http://?|||Yes, https works. Like I said, if I'm using only Server certificates, there's no problem - I've turned off non-ssl ports. Also, if I'm looking at the url in the browser with Client certificates turned on, it prompts me for a certificate, and then loads the page fine. But then if I go over to SQL Server management studio and try to synchronize my .sdf, I get the SSL error.|||

FYI, I just found this page on MSDN:

http://msdn2.microsoft.com/en-us/library/ms152511.aspx

which says "Web synchronization for merge replication supports using server certificates but not client certificates."

Can anyone verify this? Has anyone ever successfully used a client certificate with merge replication?

|||A server certificate is indeed required for web synchronization, with client certificate it will fail.