Hi ,
What do I have to do to make this option valid between
Sql Server and clients ?
I tried to mark the needed check boxes in the client network utility and in
the server network utility but it didn`t work
regards ,
DBA.Set the Force Protocol Encryption option to ON on the server by using the SQ
L Server Server Network Utility. If you turn on encryption on the server, al
l the clients must connect by using encryption and you must install a certif
icate on the server. For mo
re information, refer to the "Net-Library Encryption" topic in SQL Server 20
00 Books Online.
KBA http://support.microsoft.com/defaul...kb;EN-US;318605 for the r
eference.
BTW what was the error when you set the encryption?
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Perform
ance topic.
"w2system@.system.com" wrote:
> Hi ,
> What do I have to do to make this option valid between
> Sql Server and clients ?
> I tried to mark the needed check boxes in the client network utility and i
n
> the server network utility but it didn`t work
> regards ,
> DBA.
>
>sql
Showing posts with label client. Show all posts
Showing posts with label client. Show all posts
Wednesday, March 21, 2012
Monday, March 19, 2012
Force Encryption on client with Thawte certificate
Hello
I think that I have read every paper on the Internet about SSL and SQL
2000 and have "pains takingly" tried to implement every suggestion.
1. I have a certificate from Thawte installed on a Windows 2000 server
that also has SQL 2000 installed.
2. The name of the certificate is a FQDN.
3. I can set Force Encryption on the Server side, restart the SQL
Server and it starts normally.
4. I use network monitor and can see network traffic encrypted between
client and server.
5. When I turn off Force Encryption with the "server utility", I
restart the SQL Server.
6. I turn on Force Encrytion on the client side.
7. I try to connect to the server with SQL Query and get an SSL Error.
8. There is only one certificate on the server.
9. I set the "friendly name" on the certificate to the FQDN.
10. I have viewed the certificate and I see no errors and the path
looks good.
11. I think that I have all the SPs installed.
I am assuming that the certificate is installed correctly because when
I set Force Encryption on the server, SQL Server starts with no
problem.
When I reset Force Encryption with the "server utility" -- restart the
SQL Server -- and set Force Encryption with the "client utility", I get
the "SSL error" when I try to log in with Query analyzer.
Because the SQL Instance and the client application are on the same
machine, and because I can see the Thawte Server CA under the Authority
tab, that the client trusts Thawte. Out of desperation, I even tried to
do an export and import using IE -- though I did not think that was
necessary because both client and server are on the same mchine.
This should not be this difficult
Any help is greatly appreciated.
Thanks
Hi
I can't say I have tried this but BOL states (second sentence):
"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"
John
"norrad" wrote:
> Hello
> I think that I have read every paper on the Internet about SSL and SQL
> 2000 and have "pains takingly" tried to implement every suggestion.
> 1. I have a certificate from Thawte installed on a Windows 2000 server
> that also has SQL 2000 installed.
> 2. The name of the certificate is a FQDN.
> 3. I can set Force Encryption on the Server side, restart the SQL
> Server and it starts normally.
> 4. I use network monitor and can see network traffic encrypted between
> client and server.
> 5. When I turn off Force Encryption with the "server utility", I
> restart the SQL Server.
> 6. I turn on Force Encrytion on the client side.
> 7. I try to connect to the server with SQL Query and get an SSL Error.
> 8. There is only one certificate on the server.
> 9. I set the "friendly name" on the certificate to the FQDN.
> 10. I have viewed the certificate and I see no errors and the path
> looks good.
> 11. I think that I have all the SPs installed.
> I am assuming that the certificate is installed correctly because when
> I set Force Encryption on the server, SQL Server starts with no
> problem.
> When I reset Force Encryption with the "server utility" -- restart the
> SQL Server -- and set Force Encryption with the "client utility", I get
> the "SSL error" when I try to log in with Query analyzer.
>
> Because the SQL Instance and the client application are on the same
> machine, and because I can see the Thawte Server CA under the Authority
> tab, that the client trusts Thawte. Out of desperation, I even tried to
> do an export and import using IE -- though I did not think that was
> necessary because both client and server are on the same mchine.
>
> This should not be this difficult
> Any help is greatly appreciated.
> Thanks
>
|||Hello
Thanks
The Thawte Server CA is a trusted certification authority. It is
loaded by default with 2000 and because both SQL Server and Client are
on the same box, I should be ok and SQL server does start with the
certificate assigned.-- though I am sure that I am doing something
stupid.
Thanks for taking the time to respond. I have been working on this off
and on for two weeks. I see others with the same problem, I try to be
very carefull and implement the suggestions -- but still I get SSL
error
John Bell wrote:[vbcol=seagreen]
> Hi
> I can't say I have tried this but BOL states (second sentence):
> "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"
> John
> "norrad" wrote:
|||Hi
I found these by searching the security news group
microsoft.public.sqlserver.security, the first one gives step by step details
on how to enable SSL on a client, you may actually want to try this on a
stand alone client to make sure that nothing else is encrypted, doing it on
the server will force encryption of other things such as DTS packages run
through xp_cmdshell etc.. The second article also states "For the client to
request the SSL encryption, the client computer must trust the server
certificate and the certificate must already exist on the server" You may
want to post the exact error number and message you are getting.
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
319349 BUG: Turning On the 'Force Protocol Encryption' Option Is
Irreversible
http://support.microsoft.com/?id=319349
John
"norrad" wrote:
> Hello
> Thanks
> The Thawte Server CA is a trusted certification authority. It is
> loaded by default with 2000 and because both SQL Server and Client are
> on the same box, I should be ok and SQL server does start with the
> certificate assigned.-- though I am sure that I am doing something
> stupid.
> Thanks for taking the time to respond. I have been working on this off
> and on for two weeks. I see others with the same problem, I try to be
> very carefull and implement the suggestions -- but still I get SSL
> error
>
> John Bell wrote:
>
I think that I have read every paper on the Internet about SSL and SQL
2000 and have "pains takingly" tried to implement every suggestion.
1. I have a certificate from Thawte installed on a Windows 2000 server
that also has SQL 2000 installed.
2. The name of the certificate is a FQDN.
3. I can set Force Encryption on the Server side, restart the SQL
Server and it starts normally.
4. I use network monitor and can see network traffic encrypted between
client and server.
5. When I turn off Force Encryption with the "server utility", I
restart the SQL Server.
6. I turn on Force Encrytion on the client side.
7. I try to connect to the server with SQL Query and get an SSL Error.
8. There is only one certificate on the server.
9. I set the "friendly name" on the certificate to the FQDN.
10. I have viewed the certificate and I see no errors and the path
looks good.
11. I think that I have all the SPs installed.
I am assuming that the certificate is installed correctly because when
I set Force Encryption on the server, SQL Server starts with no
problem.
When I reset Force Encryption with the "server utility" -- restart the
SQL Server -- and set Force Encryption with the "client utility", I get
the "SSL error" when I try to log in with Query analyzer.
Because the SQL Instance and the client application are on the same
machine, and because I can see the Thawte Server CA under the Authority
tab, that the client trusts Thawte. Out of desperation, I even tried to
do an export and import using IE -- though I did not think that was
necessary because both client and server are on the same mchine.
This should not be this difficult
Any help is greatly appreciated.
Thanks
Hi
I can't say I have tried this but BOL states (second sentence):
"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"
John
"norrad" wrote:
> Hello
> I think that I have read every paper on the Internet about SSL and SQL
> 2000 and have "pains takingly" tried to implement every suggestion.
> 1. I have a certificate from Thawte installed on a Windows 2000 server
> that also has SQL 2000 installed.
> 2. The name of the certificate is a FQDN.
> 3. I can set Force Encryption on the Server side, restart the SQL
> Server and it starts normally.
> 4. I use network monitor and can see network traffic encrypted between
> client and server.
> 5. When I turn off Force Encryption with the "server utility", I
> restart the SQL Server.
> 6. I turn on Force Encrytion on the client side.
> 7. I try to connect to the server with SQL Query and get an SSL Error.
> 8. There is only one certificate on the server.
> 9. I set the "friendly name" on the certificate to the FQDN.
> 10. I have viewed the certificate and I see no errors and the path
> looks good.
> 11. I think that I have all the SPs installed.
> I am assuming that the certificate is installed correctly because when
> I set Force Encryption on the server, SQL Server starts with no
> problem.
> When I reset Force Encryption with the "server utility" -- restart the
> SQL Server -- and set Force Encryption with the "client utility", I get
> the "SSL error" when I try to log in with Query analyzer.
>
> Because the SQL Instance and the client application are on the same
> machine, and because I can see the Thawte Server CA under the Authority
> tab, that the client trusts Thawte. Out of desperation, I even tried to
> do an export and import using IE -- though I did not think that was
> necessary because both client and server are on the same mchine.
>
> This should not be this difficult
> Any help is greatly appreciated.
> Thanks
>
|||Hello
Thanks
The Thawte Server CA is a trusted certification authority. It is
loaded by default with 2000 and because both SQL Server and Client are
on the same box, I should be ok and SQL server does start with the
certificate assigned.-- though I am sure that I am doing something
stupid.
Thanks for taking the time to respond. I have been working on this off
and on for two weeks. I see others with the same problem, I try to be
very carefull and implement the suggestions -- but still I get SSL
error
John Bell wrote:[vbcol=seagreen]
> Hi
> I can't say I have tried this but BOL states (second sentence):
> "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"
> John
> "norrad" wrote:
|||Hi
I found these by searching the security news group
microsoft.public.sqlserver.security, the first one gives step by step details
on how to enable SSL on a client, you may actually want to try this on a
stand alone client to make sure that nothing else is encrypted, doing it on
the server will force encryption of other things such as DTS packages run
through xp_cmdshell etc.. The second article also states "For the client to
request the SSL encryption, the client computer must trust the server
certificate and the certificate must already exist on the server" You may
want to post the exact error number and message you are getting.
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
319349 BUG: Turning On the 'Force Protocol Encryption' Option Is
Irreversible
http://support.microsoft.com/?id=319349
John
"norrad" wrote:
> Hello
> Thanks
> The Thawte Server CA is a trusted certification authority. It is
> loaded by default with 2000 and because both SQL Server and Client are
> on the same box, I should be ok and SQL server does start with the
> certificate assigned.-- though I am sure that I am doing something
> stupid.
> Thanks for taking the time to respond. I have been working on this off
> and on for two weeks. I see others with the same problem, I try to be
> very carefull and implement the suggestions -- but still I get SSL
> error
>
> John Bell wrote:
>
Force Encryption on client with Thawte certificate
Hello
I think that I have read every paper on the Internet about SSL and SQL
2000 and have "pains takingly" tried to implement every suggestion.
1. I have a certificate from Thawte installed on a Windows 2000 server
that also has SQL 2000 installed.
2. The name of the certificate is a FQDN.
3. I can set Force Encryption on the Server side, restart the SQL
Server and it starts normally.
4. I use network monitor and can see network traffic encrypted between
client and server.
5. When I turn off Force Encryption with the "server utility", I
restart the SQL Server.
6. I turn on Force Encrytion on the client side.
7. I try to connect to the server with SQL Query and get an SSL Error.
8. There is only one certificate on the server.
9. I set the "friendly name" on the certificate to the FQDN.
10. I have viewed the certificate and I see no errors and the path
looks good.
11. I think that I have all the SPs installed.
I am assuming that the certificate is installed correctly because when
I set Force Encryption on the server, SQL Server starts with no
problem.
When I reset Force Encryption with the "server utility" -- restart the
SQL Server -- and set Force Encryption with the "client utility", I get
the "SSL error" when I try to log in with Query analyzer.
Because the SQL Instance and the client application are on the same
machine, and because I can see the Thawte Server CA under the Authority
tab, that the client trusts Thawte. Out of desperation, I even tried to
do an export and import using IE -- though I did not think that was
necessary because both client and server are on the same mchine.
This should not be this difficult
Any help is greatly appreciated.
ThanksHi
I can't say I have tried this but BOL states (second sentence):
"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"
John
"norrad" wrote:
> Hello
> I think that I have read every paper on the Internet about SSL and SQL
> 2000 and have "pains takingly" tried to implement every suggestion.
> 1. I have a certificate from Thawte installed on a Windows 2000 server
> that also has SQL 2000 installed.
> 2. The name of the certificate is a FQDN.
> 3. I can set Force Encryption on the Server side, restart the SQL
> Server and it starts normally.
> 4. I use network monitor and can see network traffic encrypted between
> client and server.
> 5. When I turn off Force Encryption with the "server utility", I
> restart the SQL Server.
> 6. I turn on Force Encrytion on the client side.
> 7. I try to connect to the server with SQL Query and get an SSL Error.
> 8. There is only one certificate on the server.
> 9. I set the "friendly name" on the certificate to the FQDN.
> 10. I have viewed the certificate and I see no errors and the path
> looks good.
> 11. I think that I have all the SPs installed.
> I am assuming that the certificate is installed correctly because when
> I set Force Encryption on the server, SQL Server starts with no
> problem.
> When I reset Force Encryption with the "server utility" -- restart the
> SQL Server -- and set Force Encryption with the "client utility", I get
> the "SSL error" when I try to log in with Query analyzer.
>
> Because the SQL Instance and the client application are on the same
> machine, and because I can see the Thawte Server CA under the Authority
> tab, that the client trusts Thawte. Out of desperation, I even tried to
> do an export and import using IE -- though I did not think that was
> necessary because both client and server are on the same mchine.
>
> This should not be this difficult
> Any help is greatly appreciated.
> Thanks
>|||Hello
Thanks
The Thawte Server CA is a trusted certification authority. It is
loaded by default with 2000 and because both SQL Server and Client are
on the same box, I should be ok and SQL server does start with the
certificate assigned.-- though I am sure that I am doing something
stupid.
Thanks for taking the time to respond. I have been working on this off
and on for two weeks. I see others with the same problem, I try to be
very carefull and implement the suggestions -- but still I get SSL
error
John Bell wrote:[vbcol=seagreen]
> Hi
> I can't say I have tried this but BOL states (second sentence):
> "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"
> John
> "norrad" wrote:
>|||Hi
I found these by searching the security news group
microsoft.public.sqlserver.security, the first one gives step by step detail
s
on how to enable SSL on a client, you may actually want to try this on a
stand alone client to make sure that nothing else is encrypted, doing it on
the server will force encryption of other things such as DTS packages run
through xp_cmdshell etc.. The second article also states "For the client to
request the SSL encryption, the client computer must trust the server
certificate and the certificate must already exist on the server" You may
want to post the exact error number and message you are getting.
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
319349 BUG: Turning On the 'Force Protocol Encryption' Option Is
Irreversible
http://support.microsoft.com/?id=319349
John
"norrad" wrote:
> Hello
> Thanks
> The Thawte Server CA is a trusted certification authority. It is
> loaded by default with 2000 and because both SQL Server and Client are
> on the same box, I should be ok and SQL server does start with the
> certificate assigned.-- though I am sure that I am doing something
> stupid.
> Thanks for taking the time to respond. I have been working on this off
> and on for two weeks. I see others with the same problem, I try to be
> very carefull and implement the suggestions -- but still I get SSL
> error
>
> John Bell wrote:
>
I think that I have read every paper on the Internet about SSL and SQL
2000 and have "pains takingly" tried to implement every suggestion.
1. I have a certificate from Thawte installed on a Windows 2000 server
that also has SQL 2000 installed.
2. The name of the certificate is a FQDN.
3. I can set Force Encryption on the Server side, restart the SQL
Server and it starts normally.
4. I use network monitor and can see network traffic encrypted between
client and server.
5. When I turn off Force Encryption with the "server utility", I
restart the SQL Server.
6. I turn on Force Encrytion on the client side.
7. I try to connect to the server with SQL Query and get an SSL Error.
8. There is only one certificate on the server.
9. I set the "friendly name" on the certificate to the FQDN.
10. I have viewed the certificate and I see no errors and the path
looks good.
11. I think that I have all the SPs installed.
I am assuming that the certificate is installed correctly because when
I set Force Encryption on the server, SQL Server starts with no
problem.
When I reset Force Encryption with the "server utility" -- restart the
SQL Server -- and set Force Encryption with the "client utility", I get
the "SSL error" when I try to log in with Query analyzer.
Because the SQL Instance and the client application are on the same
machine, and because I can see the Thawte Server CA under the Authority
tab, that the client trusts Thawte. Out of desperation, I even tried to
do an export and import using IE -- though I did not think that was
necessary because both client and server are on the same mchine.
This should not be this difficult
Any help is greatly appreciated.
ThanksHi
I can't say I have tried this but BOL states (second sentence):
"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"
John
"norrad" wrote:
> Hello
> I think that I have read every paper on the Internet about SSL and SQL
> 2000 and have "pains takingly" tried to implement every suggestion.
> 1. I have a certificate from Thawte installed on a Windows 2000 server
> that also has SQL 2000 installed.
> 2. The name of the certificate is a FQDN.
> 3. I can set Force Encryption on the Server side, restart the SQL
> Server and it starts normally.
> 4. I use network monitor and can see network traffic encrypted between
> client and server.
> 5. When I turn off Force Encryption with the "server utility", I
> restart the SQL Server.
> 6. I turn on Force Encrytion on the client side.
> 7. I try to connect to the server with SQL Query and get an SSL Error.
> 8. There is only one certificate on the server.
> 9. I set the "friendly name" on the certificate to the FQDN.
> 10. I have viewed the certificate and I see no errors and the path
> looks good.
> 11. I think that I have all the SPs installed.
> I am assuming that the certificate is installed correctly because when
> I set Force Encryption on the server, SQL Server starts with no
> problem.
> When I reset Force Encryption with the "server utility" -- restart the
> SQL Server -- and set Force Encryption with the "client utility", I get
> the "SSL error" when I try to log in with Query analyzer.
>
> Because the SQL Instance and the client application are on the same
> machine, and because I can see the Thawte Server CA under the Authority
> tab, that the client trusts Thawte. Out of desperation, I even tried to
> do an export and import using IE -- though I did not think that was
> necessary because both client and server are on the same mchine.
>
> This should not be this difficult
> Any help is greatly appreciated.
> Thanks
>|||Hello
Thanks
The Thawte Server CA is a trusted certification authority. It is
loaded by default with 2000 and because both SQL Server and Client are
on the same box, I should be ok and SQL server does start with the
certificate assigned.-- though I am sure that I am doing something
stupid.
Thanks for taking the time to respond. I have been working on this off
and on for two weeks. I see others with the same problem, I try to be
very carefull and implement the suggestions -- but still I get SSL
error
John Bell wrote:[vbcol=seagreen]
> Hi
> I can't say I have tried this but BOL states (second sentence):
> "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"
> John
> "norrad" wrote:
>|||Hi
I found these by searching the security news group
microsoft.public.sqlserver.security, the first one gives step by step detail
s
on how to enable SSL on a client, you may actually want to try this on a
stand alone client to make sure that nothing else is encrypted, doing it on
the server will force encryption of other things such as DTS packages run
through xp_cmdshell etc.. The second article also states "For the client to
request the SSL encryption, the client computer must trust the server
certificate and the certificate must already exist on the server" You may
want to post the exact error number and message you are getting.
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
319349 BUG: Turning On the 'Force Protocol Encryption' Option Is
Irreversible
http://support.microsoft.com/?id=319349
John
"norrad" wrote:
> Hello
> Thanks
> The Thawte Server CA is a trusted certification authority. It is
> loaded by default with 2000 and because both SQL Server and Client are
> on the same box, I should be ok and SQL server does start with the
> certificate assigned.-- though I am sure that I am doing something
> stupid.
> Thanks for taking the time to respond. I have been working on this off
> and on for two weeks. I see others with the same problem, I try to be
> very carefull and implement the suggestions -- but still I get SSL
> error
>
> John Bell wrote:
>
Force Encryption on client with Thawte certificate
Hello
I think that I have read every paper on the Internet about SSL and SQL
2000 and have "pains takingly" tried to implement every suggestion.
1. I have a certificate from Thawte installed on a Windows 2000 server
that also has SQL 2000 installed.
2. The name of the certificate is a FQDN.
3. I can set Force Encryption on the Server side, restart the SQL
Server and it starts normally.
4. I use network monitor and can see network traffic encrypted between
client and server.
5. When I turn off Force Encryption with the "server utility", I
restart the SQL Server.
6. I turn on Force Encrytion on the client side.
7. I try to connect to the server with SQL Query and get an SSL Error.
8. There is only one certificate on the server.
9. I set the "friendly name" on the certificate to the FQDN.
10. I have viewed the certificate and I see no errors and the path
looks good.
11. I think that I have all the SPs installed.
I am assuming that the certificate is installed correctly because when
I set Force Encryption on the server, SQL Server starts with no
problem.
When I reset Force Encryption with the "server utility" -- restart the
SQL Server -- and set Force Encryption with the "client utility", I get
the "SSL error" when I try to log in with Query analyzer.
Because the SQL Instance and the client application are on the same
machine, and because I can see the Thawte Server CA under the Authority
tab, that the client trusts Thawte. Out of desperation, I even tried to
do an export and import using IE -- though I did not think that was
necessary because both client and server are on the same mchine.
This should not be this difficult
Any help is greatly appreciated.
ThanksHi
I can't say I have tried this but BOL states (second sentence):
"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"
John
"norrad" wrote:
> Hello
> I think that I have read every paper on the Internet about SSL and SQL
> 2000 and have "pains takingly" tried to implement every suggestion.
> 1. I have a certificate from Thawte installed on a Windows 2000 server
> that also has SQL 2000 installed.
> 2. The name of the certificate is a FQDN.
> 3. I can set Force Encryption on the Server side, restart the SQL
> Server and it starts normally.
> 4. I use network monitor and can see network traffic encrypted between
> client and server.
> 5. When I turn off Force Encryption with the "server utility", I
> restart the SQL Server.
> 6. I turn on Force Encrytion on the client side.
> 7. I try to connect to the server with SQL Query and get an SSL Error.
> 8. There is only one certificate on the server.
> 9. I set the "friendly name" on the certificate to the FQDN.
> 10. I have viewed the certificate and I see no errors and the path
> looks good.
> 11. I think that I have all the SPs installed.
> I am assuming that the certificate is installed correctly because when
> I set Force Encryption on the server, SQL Server starts with no
> problem.
> When I reset Force Encryption with the "server utility" -- restart the
> SQL Server -- and set Force Encryption with the "client utility", I get
> the "SSL error" when I try to log in with Query analyzer.
>
> Because the SQL Instance and the client application are on the same
> machine, and because I can see the Thawte Server CA under the Authority
> tab, that the client trusts Thawte. Out of desperation, I even tried to
> do an export and import using IE -- though I did not think that was
> necessary because both client and server are on the same mchine.
>
> This should not be this difficult
> Any help is greatly appreciated.
> Thanks
>|||Hello
Thanks
The Thawte Server CA is a trusted certification authority. It is
loaded by default with 2000 and because both SQL Server and Client are
on the same box, I should be ok and SQL server does start with the
certificate assigned.-- though I am sure that I am doing something
stupid.
Thanks for taking the time to respond. I have been working on this off
and on for two weeks. I see others with the same problem, I try to be
very carefull and implement the suggestions -- but still I get SSL
error
John Bell wrote:
> Hi
> I can't say I have tried this but BOL states (second sentence):
> "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"
> John
> "norrad" wrote:
> > Hello
> >
> > I think that I have read every paper on the Internet about SSL and SQL
> > 2000 and have "pains takingly" tried to implement every suggestion.
> >
> > 1. I have a certificate from Thawte installed on a Windows 2000 server
> > that also has SQL 2000 installed.
> > 2. The name of the certificate is a FQDN.
> > 3. I can set Force Encryption on the Server side, restart the SQL
> > Server and it starts normally.
> > 4. I use network monitor and can see network traffic encrypted between
> > client and server.
> > 5. When I turn off Force Encryption with the "server utility", I
> > restart the SQL Server.
> > 6. I turn on Force Encrytion on the client side.
> > 7. I try to connect to the server with SQL Query and get an SSL Error.
> > 8. There is only one certificate on the server.
> > 9. I set the "friendly name" on the certificate to the FQDN.
> > 10. I have viewed the certificate and I see no errors and the path
> > looks good.
> > 11. I think that I have all the SPs installed.
> >
> > I am assuming that the certificate is installed correctly because when
> > I set Force Encryption on the server, SQL Server starts with no
> > problem.
> >
> > When I reset Force Encryption with the "server utility" -- restart the
> > SQL Server -- and set Force Encryption with the "client utility", I get
> > the "SSL error" when I try to log in with Query analyzer.
> >
> >
> > Because the SQL Instance and the client application are on the same
> > machine, and because I can see the Thawte Server CA under the Authority
> > tab, that the client trusts Thawte. Out of desperation, I even tried to
> > do an export and import using IE -- though I did not think that was
> > necessary because both client and server are on the same mchine.
> >
> >
> >
> > This should not be this difficult
> >
> > Any help is greatly appreciated.
> >
> > Thanks
> >
> >|||Hi
I found these by searching the security news group
microsoft.public.sqlserver.security, the first one gives step by step details
on how to enable SSL on a client, you may actually want to try this on a
stand alone client to make sure that nothing else is encrypted, doing it on
the server will force encryption of other things such as DTS packages run
through xp_cmdshell etc.. The second article also states "For the client to
request the SSL encryption, the client computer must trust the server
certificate and the certificate must already exist on the server" You may
want to post the exact error number and message you are getting.
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
319349 BUG: Turning On the 'Force Protocol Encryption' Option Is
Irreversible
http://support.microsoft.com/?id=319349
John
"norrad" wrote:
> Hello
> Thanks
> The Thawte Server CA is a trusted certification authority. It is
> loaded by default with 2000 and because both SQL Server and Client are
> on the same box, I should be ok and SQL server does start with the
> certificate assigned.-- though I am sure that I am doing something
> stupid.
> Thanks for taking the time to respond. I have been working on this off
> and on for two weeks. I see others with the same problem, I try to be
> very carefull and implement the suggestions -- but still I get SSL
> error
>
> John Bell wrote:
> > Hi
> >
> > I can't say I have tried this but BOL states (second sentence):
> >
> > "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"
> >
> > John
> >
> > "norrad" wrote:
> >
> > > Hello
> > >
> > > I think that I have read every paper on the Internet about SSL and SQL
> > > 2000 and have "pains takingly" tried to implement every suggestion.
> > >
> > > 1. I have a certificate from Thawte installed on a Windows 2000 server
> > > that also has SQL 2000 installed.
> > > 2. The name of the certificate is a FQDN.
> > > 3. I can set Force Encryption on the Server side, restart the SQL
> > > Server and it starts normally.
> > > 4. I use network monitor and can see network traffic encrypted between
> > > client and server.
> > > 5. When I turn off Force Encryption with the "server utility", I
> > > restart the SQL Server.
> > > 6. I turn on Force Encrytion on the client side.
> > > 7. I try to connect to the server with SQL Query and get an SSL Error.
> > > 8. There is only one certificate on the server.
> > > 9. I set the "friendly name" on the certificate to the FQDN.
> > > 10. I have viewed the certificate and I see no errors and the path
> > > looks good.
> > > 11. I think that I have all the SPs installed.
> > >
> > > I am assuming that the certificate is installed correctly because when
> > > I set Force Encryption on the server, SQL Server starts with no
> > > problem.
> > >
> > > When I reset Force Encryption with the "server utility" -- restart the
> > > SQL Server -- and set Force Encryption with the "client utility", I get
> > > the "SSL error" when I try to log in with Query analyzer.
> > >
> > >
> > > Because the SQL Instance and the client application are on the same
> > > machine, and because I can see the Thawte Server CA under the Authority
> > > tab, that the client trusts Thawte. Out of desperation, I even tried to
> > > do an export and import using IE -- though I did not think that was
> > > necessary because both client and server are on the same mchine.
> > >
> > >
> > >
> > > This should not be this difficult
> > >
> > > Any help is greatly appreciated.
> > >
> > > Thanks
> > >
> > >
>
I think that I have read every paper on the Internet about SSL and SQL
2000 and have "pains takingly" tried to implement every suggestion.
1. I have a certificate from Thawte installed on a Windows 2000 server
that also has SQL 2000 installed.
2. The name of the certificate is a FQDN.
3. I can set Force Encryption on the Server side, restart the SQL
Server and it starts normally.
4. I use network monitor and can see network traffic encrypted between
client and server.
5. When I turn off Force Encryption with the "server utility", I
restart the SQL Server.
6. I turn on Force Encrytion on the client side.
7. I try to connect to the server with SQL Query and get an SSL Error.
8. There is only one certificate on the server.
9. I set the "friendly name" on the certificate to the FQDN.
10. I have viewed the certificate and I see no errors and the path
looks good.
11. I think that I have all the SPs installed.
I am assuming that the certificate is installed correctly because when
I set Force Encryption on the server, SQL Server starts with no
problem.
When I reset Force Encryption with the "server utility" -- restart the
SQL Server -- and set Force Encryption with the "client utility", I get
the "SSL error" when I try to log in with Query analyzer.
Because the SQL Instance and the client application are on the same
machine, and because I can see the Thawte Server CA under the Authority
tab, that the client trusts Thawte. Out of desperation, I even tried to
do an export and import using IE -- though I did not think that was
necessary because both client and server are on the same mchine.
This should not be this difficult
Any help is greatly appreciated.
ThanksHi
I can't say I have tried this but BOL states (second sentence):
"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"
John
"norrad" wrote:
> Hello
> I think that I have read every paper on the Internet about SSL and SQL
> 2000 and have "pains takingly" tried to implement every suggestion.
> 1. I have a certificate from Thawte installed on a Windows 2000 server
> that also has SQL 2000 installed.
> 2. The name of the certificate is a FQDN.
> 3. I can set Force Encryption on the Server side, restart the SQL
> Server and it starts normally.
> 4. I use network monitor and can see network traffic encrypted between
> client and server.
> 5. When I turn off Force Encryption with the "server utility", I
> restart the SQL Server.
> 6. I turn on Force Encrytion on the client side.
> 7. I try to connect to the server with SQL Query and get an SSL Error.
> 8. There is only one certificate on the server.
> 9. I set the "friendly name" on the certificate to the FQDN.
> 10. I have viewed the certificate and I see no errors and the path
> looks good.
> 11. I think that I have all the SPs installed.
> I am assuming that the certificate is installed correctly because when
> I set Force Encryption on the server, SQL Server starts with no
> problem.
> When I reset Force Encryption with the "server utility" -- restart the
> SQL Server -- and set Force Encryption with the "client utility", I get
> the "SSL error" when I try to log in with Query analyzer.
>
> Because the SQL Instance and the client application are on the same
> machine, and because I can see the Thawte Server CA under the Authority
> tab, that the client trusts Thawte. Out of desperation, I even tried to
> do an export and import using IE -- though I did not think that was
> necessary because both client and server are on the same mchine.
>
> This should not be this difficult
> Any help is greatly appreciated.
> Thanks
>|||Hello
Thanks
The Thawte Server CA is a trusted certification authority. It is
loaded by default with 2000 and because both SQL Server and Client are
on the same box, I should be ok and SQL server does start with the
certificate assigned.-- though I am sure that I am doing something
stupid.
Thanks for taking the time to respond. I have been working on this off
and on for two weeks. I see others with the same problem, I try to be
very carefull and implement the suggestions -- but still I get SSL
error
John Bell wrote:
> Hi
> I can't say I have tried this but BOL states (second sentence):
> "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"
> John
> "norrad" wrote:
> > Hello
> >
> > I think that I have read every paper on the Internet about SSL and SQL
> > 2000 and have "pains takingly" tried to implement every suggestion.
> >
> > 1. I have a certificate from Thawte installed on a Windows 2000 server
> > that also has SQL 2000 installed.
> > 2. The name of the certificate is a FQDN.
> > 3. I can set Force Encryption on the Server side, restart the SQL
> > Server and it starts normally.
> > 4. I use network monitor and can see network traffic encrypted between
> > client and server.
> > 5. When I turn off Force Encryption with the "server utility", I
> > restart the SQL Server.
> > 6. I turn on Force Encrytion on the client side.
> > 7. I try to connect to the server with SQL Query and get an SSL Error.
> > 8. There is only one certificate on the server.
> > 9. I set the "friendly name" on the certificate to the FQDN.
> > 10. I have viewed the certificate and I see no errors and the path
> > looks good.
> > 11. I think that I have all the SPs installed.
> >
> > I am assuming that the certificate is installed correctly because when
> > I set Force Encryption on the server, SQL Server starts with no
> > problem.
> >
> > When I reset Force Encryption with the "server utility" -- restart the
> > SQL Server -- and set Force Encryption with the "client utility", I get
> > the "SSL error" when I try to log in with Query analyzer.
> >
> >
> > Because the SQL Instance and the client application are on the same
> > machine, and because I can see the Thawte Server CA under the Authority
> > tab, that the client trusts Thawte. Out of desperation, I even tried to
> > do an export and import using IE -- though I did not think that was
> > necessary because both client and server are on the same mchine.
> >
> >
> >
> > This should not be this difficult
> >
> > Any help is greatly appreciated.
> >
> > Thanks
> >
> >|||Hi
I found these by searching the security news group
microsoft.public.sqlserver.security, the first one gives step by step details
on how to enable SSL on a client, you may actually want to try this on a
stand alone client to make sure that nothing else is encrypted, doing it on
the server will force encryption of other things such as DTS packages run
through xp_cmdshell etc.. The second article also states "For the client to
request the SSL encryption, the client computer must trust the server
certificate and the certificate must already exist on the server" You may
want to post the exact error number and message you are getting.
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
319349 BUG: Turning On the 'Force Protocol Encryption' Option Is
Irreversible
http://support.microsoft.com/?id=319349
John
"norrad" wrote:
> Hello
> Thanks
> The Thawte Server CA is a trusted certification authority. It is
> loaded by default with 2000 and because both SQL Server and Client are
> on the same box, I should be ok and SQL server does start with the
> certificate assigned.-- though I am sure that I am doing something
> stupid.
> Thanks for taking the time to respond. I have been working on this off
> and on for two weeks. I see others with the same problem, I try to be
> very carefull and implement the suggestions -- but still I get SSL
> error
>
> John Bell wrote:
> > Hi
> >
> > I can't say I have tried this but BOL states (second sentence):
> >
> > "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"
> >
> > John
> >
> > "norrad" wrote:
> >
> > > Hello
> > >
> > > I think that I have read every paper on the Internet about SSL and SQL
> > > 2000 and have "pains takingly" tried to implement every suggestion.
> > >
> > > 1. I have a certificate from Thawte installed on a Windows 2000 server
> > > that also has SQL 2000 installed.
> > > 2. The name of the certificate is a FQDN.
> > > 3. I can set Force Encryption on the Server side, restart the SQL
> > > Server and it starts normally.
> > > 4. I use network monitor and can see network traffic encrypted between
> > > client and server.
> > > 5. When I turn off Force Encryption with the "server utility", I
> > > restart the SQL Server.
> > > 6. I turn on Force Encrytion on the client side.
> > > 7. I try to connect to the server with SQL Query and get an SSL Error.
> > > 8. There is only one certificate on the server.
> > > 9. I set the "friendly name" on the certificate to the FQDN.
> > > 10. I have viewed the certificate and I see no errors and the path
> > > looks good.
> > > 11. I think that I have all the SPs installed.
> > >
> > > I am assuming that the certificate is installed correctly because when
> > > I set Force Encryption on the server, SQL Server starts with no
> > > problem.
> > >
> > > When I reset Force Encryption with the "server utility" -- restart the
> > > SQL Server -- and set Force Encryption with the "client utility", I get
> > > the "SSL error" when I try to log in with Query analyzer.
> > >
> > >
> > > Because the SQL Instance and the client application are on the same
> > > machine, and because I can see the Thawte Server CA under the Authority
> > > tab, that the client trusts Thawte. Out of desperation, I even tried to
> > > do an export and import using IE -- though I did not think that was
> > > necessary because both client and server are on the same mchine.
> > >
> > >
> > >
> > > This should not be this difficult
> > >
> > > Any help is greatly appreciated.
> > >
> > > Thanks
> > >
> > >
>
Force Encryption Login not working
Hi,
i've enabled the certification authentication login i use SQL 2K5 ENT
my problem is like that: in the client network utility i check the Force protocol encryption and connect
to the server the client can't connect because he doesn't have the certificate install on his machine which it's OK.
the problem is when i remove the Force protocol encryption on the client he connect successfully without the
certificate install on his machine or any thing.
i double check that on the server i've YES on the Force Encryption in the protocols For MSSQLSERVER.
why is that?
THX
Hi Avi,
To clarify, SQL Server does not support any certificate authentication -- certificates are used to facilitate SSL encryption only. When encryption is forced on the client-side, the client will perform server validation to ensure that the certificate came from the correct host. In order for this validation to succeed, the client must trust the root signing authority of the certificate. If the server, and not the client, enforces encryption, then the client will not perform server validation. Furthermore, if a certificate hasn't been provisioned on the server machine, the server will use its self-generated cert for channel encryption. Obviously, the self-generated cert would fail server validation (see this post for more details: http://blogs.msdn.com/dataaccess/archive/2005/08/05/448401.aspx) so this is something to watch out for when forcing client-side encryption. I believe that this explains the behaviour that you are seeing.
Il-Sung.
Force client components using a commmand line installation to diffrent location
Is it possible to force the SLQ 2005 Client Components to be installed into a diffrent location other than the default location?
What I'm trying to do is run the setup command though the command line to create an unattended client installation with all the client componets to a diffrent location that
Thanks, Any information or examples would be great!
There is a way to specify client component location is setup or on the command line but it can only be used for the first time SQL Server 2005 is installed on a machine. Once there is any product or instance of SQL Server 2005 on the machine this location cannot be changed. To specify the location on the command line you can use INSTALLSQLSHAREDDIR.
Note: there are still some components going under Program Files\Microsoft SQL Server\90 which location cannot be changed. But most of the client components is installed to INSTALLSQLSHAREDDIR.
For example:
setup.exe INSTALLSQLSHAREDDIR=D:\SQLTools ADDLOCAL=Client_Components,Connectivity,SQL_Tools90,SQL_WarehouseDevWorkbench /qb
ADDLOCAL lists all features you want to install. See template.ini in the same location as setup.exe for whole list of features.
Wednesday, March 7, 2012
FOR XML EXPLICIT and namespaces
I'm trying to get a fairly complex structure from SQL Server 2000 to a client via classes generated with an xsd file and transferred through Web Services.
I can get the SQL stuff to work nicely if I have no tragetNamespace in the xsd.
However, then I cannot create a client proxy for my web service.
If I specify a (matching) targetNamespace in the web service description and the xsd file then I can create a proxy. However, at this point the XML produced by the SQL FOR XML EXPLICIT no longer validates.
I imagine this will be fixed if I specifiy a target namespace for my FOR XML EXPLICIT query (that is the root element should have a targetNamespace attribute).
I cannot see how to do this. There is sem reference to templates, but I believe these are use from http and I can't see any logical way to embed these in my SPROCS or ExecuteXmlReader calls.
Iain
"Iain" wrote:
> I'm trying to get a fairly complex structure from SQL Server 2000 to a client via classes generated with an xsd file and transferred through Web Services.
> I can get the SQL stuff to work nicely if I have no tragetNamespace in the xsd.
...
Well, I found somewhere someone putting the xmlns in as a separate column in the select query. So ...
'http://idcl.co.uk/PBWebAdmin' as [OrderBatch!1!xmlns],
and equivalent nulls in all the union locations.
This resulted in a query that worked sort of. Now for some reason it was rejecting the name and address elements (<FullName ..>, <Add1 ..>) in a <DeliveryAddress> element a couple of nests down.
I fixed this by changing the elements in the element to attributes.
I don't know why this worked.
Iain
|||Adding xmlns or xmlns:foo (and then using !foo:bar) columns as you do are
the way to add namespaces in FOR XML currently (note that the PATH mode in
Beta2 will not support this and I am working on getting a better solution
for at least the PATH mode made available in Beta3).
If you use xmlns without specifying a prefix, all descending elements will
be in that namespace (unless another xmlns overwrites it). If your schema
does not allow those elements to be in that namespace, you may have a
problem.
Attributes on the other hand are never part of the default namespace. They
belong to the element unless an explicit namespace prefix has been
associated.
So this difference may account for what you see.
HTH
Michael
"Iain" <Iain@.discussions.microsoft.com> wrote in message
news:4B201E22-E0AF-4B12-9D98-E8B2DE3BD143@.microsoft.com...
> "Iain" wrote:
> ...
> Well, I found somewhere someone putting the xmlns in as a separate column
> in the select query. So ...
> 'http://idcl.co.uk/PBWebAdmin' as [OrderBatch!1!xmlns],
> and equivalent nulls in all the union locations.
> This resulted in a query that worked sort of. Now for some reason it was
> rejecting the name and address elements (<FullName ..>, <Add1 ..>) in a
> <DeliveryAddress> element a couple of nests down.
> I fixed this by changing the elements in the element to attributes.
> I don't know why this worked.
> Iain
>
I can get the SQL stuff to work nicely if I have no tragetNamespace in the xsd.
However, then I cannot create a client proxy for my web service.
If I specify a (matching) targetNamespace in the web service description and the xsd file then I can create a proxy. However, at this point the XML produced by the SQL FOR XML EXPLICIT no longer validates.
I imagine this will be fixed if I specifiy a target namespace for my FOR XML EXPLICIT query (that is the root element should have a targetNamespace attribute).
I cannot see how to do this. There is sem reference to templates, but I believe these are use from http and I can't see any logical way to embed these in my SPROCS or ExecuteXmlReader calls.
Iain
"Iain" wrote:
> I'm trying to get a fairly complex structure from SQL Server 2000 to a client via classes generated with an xsd file and transferred through Web Services.
> I can get the SQL stuff to work nicely if I have no tragetNamespace in the xsd.
...
Well, I found somewhere someone putting the xmlns in as a separate column in the select query. So ...
'http://idcl.co.uk/PBWebAdmin' as [OrderBatch!1!xmlns],
and equivalent nulls in all the union locations.
This resulted in a query that worked sort of. Now for some reason it was rejecting the name and address elements (<FullName ..>, <Add1 ..>) in a <DeliveryAddress> element a couple of nests down.
I fixed this by changing the elements in the element to attributes.
I don't know why this worked.
Iain
|||Adding xmlns or xmlns:foo (and then using !foo:bar) columns as you do are
the way to add namespaces in FOR XML currently (note that the PATH mode in
Beta2 will not support this and I am working on getting a better solution
for at least the PATH mode made available in Beta3).
If you use xmlns without specifying a prefix, all descending elements will
be in that namespace (unless another xmlns overwrites it). If your schema
does not allow those elements to be in that namespace, you may have a
problem.
Attributes on the other hand are never part of the default namespace. They
belong to the element unless an explicit namespace prefix has been
associated.
So this difference may account for what you see.
HTH
Michael
"Iain" <Iain@.discussions.microsoft.com> wrote in message
news:4B201E22-E0AF-4B12-9D98-E8B2DE3BD143@.microsoft.com...
> "Iain" wrote:
> ...
> Well, I found somewhere someone putting the xmlns in as a separate column
> in the select query. So ...
> 'http://idcl.co.uk/PBWebAdmin' as [OrderBatch!1!xmlns],
> and equivalent nulls in all the union locations.
> This resulted in a query that worked sort of. Now for some reason it was
> rejecting the name and address elements (<FullName ..>, <Add1 ..>) in a
> <DeliveryAddress> element a couple of nests down.
> I fixed this by changing the elements in the element to attributes.
> I don't know why this worked.
> Iain
>
Subscribe to:
Posts (Atom)