Results 1 to 7 of 7

Thread: Passing an encrypted password into SQL Server from VB.Net

  1. #1

    Thread Starter
    Fanatic Member InvisibleDuncan's Avatar
    Join Date
    May 2001
    Location
    Eating jam.
    Posts
    819

    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.
    Indecisiveness is the key to flexibility.

    www.mangojacks.com

  2. #2
    Addicted Member thetimmer's Avatar
    Join Date
    Jan 2014
    Location
    Plano, Texas
    Posts
    243

    Re: Passing an encrypted password into SQL Server from VB.Net

    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.
    _____________
    Tim

    If anyone's answer has helped you, please show your appreciation by rating that answer.
    When you get a solution to your issue remember to mark the thread Resolved.


    reference links

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Passing an encrypted password into SQL Server from VB.Net

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Passing an encrypted password into SQL Server from VB.Net

    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.

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Passing an encrypted password into SQL Server from VB.Net

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Fanatic Member InvisibleDuncan's Avatar
    Join Date
    May 2001
    Location
    Eating jam.
    Posts
    819

    Re: Passing an encrypted password into SQL Server from VB.Net

    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 View Post
    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.
    Indecisiveness is the key to flexibility.

    www.mangojacks.com

  7. #7
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Passing an encrypted password into SQL Server from VB.Net

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width