Connect to SQL Server db in a secured way

[Deepak Sakpal]

we have few vb apps running that connects sql server db. we have hard coded
the usernames & passwords. In this case all the programmers knows the
usernames & passwords. They can harm databases. We want to take that things
out of the apps. So the question is how to connect to sql server db in such
a way that programmer should not know the usernames & passwords.
We can create System DSN but it requies username & password along with
DSN name. Any suggestions guys.

[szlamany]

The suggestion from MICROSOFT and the one we follow is to only use WINDOWS INTEGRATED SECURITY.

That way the WINDOWS USERNAME is associated with the rights to data, tables, etc. You can remove a WINDOWS USERNAME at any time from any access level.

No password required for this at all.

SQL Authentication stores passwords in a table that can be hacked - it is not a secure method at all.

Somewhere on the MSDN site is a good white paper explaining the differences between WINDOWS and SQL authentication...

[Qualm]

As szlamany suggested, use windows integrated security.

The database admin would then grant user rights to the database and/or stored procedures directly in SQL Server, by using Enterprise Manager. Of course you'd have to be careful to restrict the programmers to the rights you wanted them to have.

An alternative is to separate your data connection code into a .dll, which is maintained by a trusted person, and have the programmers call the .dll. You could thus hide the usernames and passwords there.

[Maverickz]

As Qualm said, you could provide them with a DB connection API that you write that does all the DB connections and prevents them from having access to the DB connection info.

You can take this one step farther and use an XML file to store encrypted connection info. Everything from the DB Server name, to the DB Name, and the ID's and passwords used to connect. Your API would have the decryption key built in so it could read the XML file and make the connection while the developers could not. This also makes changing info like the password or if the DB moves to an upgraded server easier. You would just give the Devs an updated XML file.

The Mav

[szlamany]

MS still recommends against anything but WINDOWS AUTHENTICATION - for many, many reasons - so if you are about to visit this issue, consider those opinions.

We only use STORED PROCEDURES - so no TABLE access is granted to any WINDOWS users - so maybe that make our choice of WINDOWS INTEGRATED security that much easier.

The Tech dept loves it - since they have to manage 1000+ users and giving them just AD/E-mail access is a nightmare unto itself. They group the users in WINDOWS and then assign those GROUPS the rights necessary to the DB.

We use an INI file that tells our app what SERVER and DB name to connect to. We also have a line in the .INI to say whether the APP is OFF or ON at the moment - to keep users from connecting if we need emergency time down (we keep the .EXE on several main servers - so each building calls the app from a server - not hard install on each workstation.)

After it opens the .INI file and does it's initial connect, it calls to the DB to say what VERSION of the EXECUTABLE is permitted to run - if it's not a match - it aborts.

[Deepak Sakpal]

all examples i found about WINDOWS INTEGRATED SECURITY are related to .NET. I have vb6 apps.

[szlamany]

Here's some VB6 code to do a connect:

If strDriver = "" Then
        strDriver = "SQLOLEDB"
    End If
    
    'Create a connection to the database
    Set gCn = New ADODB.Connection
    
    gCn.Provider = strDriver
    gCn.Properties("Data Source").Value = strServer
    gCn.Properties("Initial Catalog").Value = strDatabase
    If gstrUser <> "" Then
        gCn.Properties("User Id").Value = gstrUser
        gCn.Properties("Password").Value = gstrPassWord
    Else
        gCn.Properties("Integrated Security").Value = "SSPI"
    End If
    
    gCn.CommandTimeout = 300
    gCn.Open
    
    'Check the connection state
    If gCn.State = adStateOpen Then
        EstablishConnection = True
    Else
        EstablishConnection = False
    End If

[Deepak Sakpal]

szlamany, thanks for the reply.

Here username & password will be stored in gstrUser & gstrPassWord variables. That means programmer will know the username & password and that is what my boss don't want. What my Boss says is : How to connect to Sql Server db in such a way that programmer will not be able to know the password.

[szlamany]

That is a generic connection routine.

The gstrUser is in an IF STATEMENT. If it is left blank - the mode you want to use - then the connection is made with WINDOWS INTEGRATED SECURITY.

Search MSDN for SSPI.

I just did and got these links:

MSDN Search for SSPI