Passing an encrypted password into SQL Server from VB.Net

We have a few VB.Net applications that connect to a SQL Server 2008 database using SQL Server authentication. Basically, we have a config file that contains an encrypted password, and our applications take that, decrypt it, and put the decrypted password in the connection string.

This obviously isn't completely secure, because if nothing else, when a developer is debugging the code he will be able to see the decrypted password.

So, my question is: is it possible to pass an encrypted password to SQL Server via the connection string and have it decrypted by SQL Server itself? I've seen a few comments about hashing a password, but I'm not sure that that would give me what I need (it's highly possible that I've fundamentally misunderstood the process, of course).

Any suggestions would be gratefully received. :thumb:

You should look at using CLR in SQL

You basically send an encrypted password through your app and have a .dll with the decrypt side of the communication sitting in the bin directory on the the SQL server so your stored procs can call it. Sorry but I haven't implemented this myself but I know it's possible.

ah... no... either hashing would work nor would using CLR in SQL...

The hashing you've probably come across is in relation to USER tables in a database... like when you sign into VBF here... your password is hashed and sent to the database... in the database there is a user's table that has your user name and a hashed form of your password... the two hashes are compared... that won't work for you because you're trying to encrypt the connection password needed for hte connection.

CLR in SQL won't work because, well it just acts the same as a stored proc... so in order to use it you have to first connect to the server/database... and that's why that won't work.


As for developers seeing the password... is that really a big deal? They shouldn't have access to sensitive data in the first place. And the development environments shouldn't have the same password as the production.

ptg

To take it back a step further... even if you could send the encrypted password, it would still be as insecure as the current situation (unless you could pass 'invisible' characters like Chr(1) etc, but I doubt that would be possible), because the value can still be grabbed and used in the same ways that the unencrypted version could be.

Just about the only way to protect it is through a webservice or something... where you never directly communicate with the database, but rather send a request to a server, and then that server makes the connection, gets the data, and passes it back to the calling program.

-tg

In all honesty, as a developer myself I'm fairly relaxed about developers being able to see the password. Unfortunately, one of our team has been using these credentials to make changes to a database on a test server because he was fed up with waiting for the 3-4 hours it takes the DBAs to release any changes. This doesn't bother me in the least (I think it's ridiculous that we can't make changes in a test database ourselves), but the DBAs have got a little tetchy. They've asked me to see whether it's possible to set these up in a way that prevents us from decrypting any passwords.

It would have been handy if SQL Server had an inbuilt encryption / decryption function that couldn't be used to log on via Management Studio. Still not secure against nefarious activity because we could still attach via our code to do things we shouldn't, but it would have got us past the immediate complaint which is to safeguard against casual abuse of the system.

Quote:

---

Originally Posted by techgnome

The hashing you've probably come across is in relation to USER tables in a database... like when you sign into VBF here... your password is hashed and sent to the database... in the database there is a user's table that has your user name and a hashed form of your password... the two hashes are compared... that won't work for you because you're trying to encrypt the connection password needed for hte connection.

---

Yep, that's pretty much how I understood it.

Thanks, guys - I guess we'll have to stick with the status quo. I can't see them going for the time it would take to develop a webservice for this.

Why not just wrap-up the Connection-Object in a Class which implements the appropriate interface -
and force your developers to work *through* that wrapped Connection-Instance (which hides
the original one, including its connection-strings and passwords) then.

This wrapper could be instantiated from a precompiled binary, which e.g. the DBAs provide
from Source only they have access to.

Olaf