SQL Login

Question

Post reply

SQL Login

AVB

SSChampion

Points: 11418
More actions
March 15, 2006 at 9:22 am

#93668

Are SQL Login connections encrypted? We have remote developers that login to our DB Server with their SQL Login that we have created. But my concern is (like FTP) the credentials are passed un-encrypted over the internet. TIA.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

By default, yes, but the method of "encryption" is very, very weak. Check the following:

SQL Server Security: Login Weaknesses

K. Brian Kelley
@kbriankelley

AVB SSChampion Points: 11418 More actions · Answer 2

Thanks for the reply I'll check out that link. I'm assuming you can't do this but it's worth a shot.... I would like to create a domain user account add that to the SQL Logins and give it appropiate access, however the remote developers are not on our domain (and may not even use windows all the time). Could they still use this Windows login to access the DB? (is there somewhere to specify domain prefix if they access via EM? Could they select SQL Login then put the domain prefix\username in the "Login Name" Field) I know in IIS you can create a local account that is the same name and password as a domain account as a work around for access to files etc... Could this be applied here?

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

You could do the local account, thing, yes. However, a more feasible solution might be to install an SSL certificate and force encryption on the connection. Is that an option? That would ensure the logins, etc., are sent encrypted.

K. Brian Kelley
@kbriankelley

AVB SSChampion Points: 11418 More actions · Answer 4

Yes I could install a Certificate......I could just generate a local one (we don't have a domain wide CA installed yet) but I am working on it so I can setup EFS on a least our DB Backups and hopefully the DB itself. Obviously I've never enforced SSL connections for SQL....where do I specify SSL connections? Is it a server wide setting or can it be specified per user? Thanks for your help.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

If you generate a local one, make sure the computer SQL Server is running on trusts the CA that generated it (which is should if it's local). As far as enforcing encrypted connections, you set this at only one place. You can set it at the server using Server Network utility, but that requires it for all clients. Or you can set it per client using Client Network Utility (cliconfg.exe) on each individual client machine. However, if you want to enforce encryption, there's only one way to go.

As far as EFS, I think the # I saw from Microsoft is about a 20% performance degradation when you use EFS on the database files. Take that into account. Also, make sure you have your recovery agents properly defined (in the event the login running SQL Server gets deleted, you'll still be able to decrypt the database files).

K. Brian Kelley
@kbriankelley

AVB SSChampion Points: 11418 More actions · Answer 6

Thanks much....I'm going to enforce the SSL connections. As far as EFS I'm going to apply that to the backup files at least. 20% is more than I'd like to lose...maybe I won't be applying that to the DB. But I had an idea....I could move all the extremly sensitive data to another DB and just encrypt that one which would leave the bulk of the data in the faster performing database. Have you ever seen anyone do this?