How can I turn off the encrypted login feature?

Hi all.

My question is, is there any option that turning of the login encrypt?

I've read many post that they said the login encryption is always on and can't turn that off.
With MSSQL2K, I made some program that audit who try to login and when and access which db.
But I can't check with MSSQL2K5 because the server and client always encrypt the login info (via TLS?)

Someone said that profile the client but when the site is busy, that feature give heavy stress to server..
The port mirror server is another machine so I need 1. decrypt the login info or 2. turn off the login encryption feature.
Is there any possible way to solve this?

Thanks for reading this.

If your goal is to audit SQL Server login's and access to SQL Server objects, you can consider SQL Server auditing. E.g., the following link in SQL Server 2005 books online describes how to turn on Login Auditing through SQL Server Management Studio: http://msdn2.microsoft.com/en-US/library/ms175850.aspx.

|||

Yes, my goal is audit the login information and other info also.

But, audit in the local server do the heavy stress to itself and also need more and more hard disk space. Especially the site QPS is more than 10000..

So, there is no way to turn off the login encryption?

Anyway, thanks for your answer, it's very helpful information.

|||

Hi Hopi,

No, there is no way to turn of login encryption for SQL Server 2005. The server will always encrypt the login packet whenever possible. And with the server's new capability of generating a self-signed cert, this most of the time.

Il-Sung.

|||

Hi. Il-Sung.

Thank you for your answer. And here's final question.

1. Is the self-signed cert generated whenever the SQL service stared? Or that cert generated only once that installed into server?

2. Where can I found that cert?

3. Is there any document for this?

Hopi

|||

Hi Hopi,

I apologize for the delay. Here are the answers to your questions:

1 - The self-signed cert is generated each time the service is started

2 - The cert is stored internally and is not accessible or queryable by any external interface

3 - The BOL topic Encrypting Connections to SQL Server mentions this:

"By default, credentials transmitted when a client application connects to SQL Server 2005 are encrypted. If a certificate signed by a mutually-trusted certification authority is not available, the self-signed certificate will be used."

Thanks,
Il-Sung

|||

Thank for your kind answer.

I still didn't resolve this but maybe a week or so.. I think I can fix our solution. Not by turning off the encryption.
Thanks again!

B/R
Hopi