VB2005 - Secure Access to SQL Server through Application Only

[Matt]

I have been searching around for quite a while now, trying to figure out
how to securely connect a Windows Forms application to an instance of
SQL Server 2005. I have looked through SQL Server Books Online, Visual
Studio's online help, and searched through newsgroups, forums, and
anything else I could think of without any avail.

The problem is as follows:
If I use SQL Server Authentication (i.e., username/password) to log into
the server, these values are stored as plain-text in the application
configuration file, which an end-user could simply open with notepad. I
have not seen any effective way to handle encrypting this file for
Windows Forms (though there appear to be methods that work under ASP.NET).

If, instead, I use Windows Authentication, I have to give all users the
necessary read/write access to perform the functions of the application.
This means that any user smart enough to download and run SQL Management
Studio Express can fool around in the database directly.

I have been pointed a couple times to look into Application Roles in SQL
Server. I understand completely how to implement these within the
context of SQL Server itself, but have not been able to find any method
of integrating them into the Visual Studio development environment. Is
there some simple way of telling my application to always connect and
register itself to a given application role?

On another note, am I even approaching this in the correct manner. Is
there some other simple method for handling all of the security issues?

Thanks,
Matt

 

[Vijay]

I would go with SQL Server authentication and try to store the ID/PWD
encrypted on the client side within a XML, text file.. whatever maybe your
choice or comfort.. There are several examples how to encrypt/decrypt
strings with 2003/1.1 you can easily apply the same to 2005 ...Or might
even be easier in 2005/.NET 2.0

VJ

 

[Matt]

VJ,
Even if I am able to encrypt the password and user ID in the file on the
client side, how would I tell the application to decrypt them each time
it needed to access the database? Since it automatically tries to handle
opening and closing connections based on the connection string, I would
need some way to inject the UID/Pass into the string. Do you have any
specific tutorials you could forward me to?

Many thanks,
Matt

 

[Stephany Young]

Have a look at the String class for methods that provide mainuplation
functionality.

Also have a look at the various Encryption classes.

 

[Matt]

I guess I don't understand how to make myself clear here. I understand
how to manipulate strings. My question is when I should be modifying the
connection string (do I need to modify it when the application starts up
and then change it back when I quit, or do I just need to modify
something once?) and is there any way to insert something into the
string to make it use an application role? It may seem simple to
everyone else, but if someone could take the time to give a clear
explanation rather than just saying "see this" or "see that", I would
greatly appreciate it. It seems odd to me that it should be this
challenging to securely connect two systems that were theoretically
designed to work together.

Thanks,
Matt

 

[Matt]

As a further note, it does not appear you can edit the connection string
within the context of the application; it is considered a read-only
property. Is there any way to get around this?

 

[Luke Zhang [MSFT]]

Hello,

An ADO connection's connection string cannot be changed when it is opened.
So if you want change a connection string, first close the connection, and
then re-open it after you change the string.

An application role was activated with the sp_setapprole built-in stored
procedure, This stored procedure requires the role name and a password. If
you use this approach, you must securely store the role name and password
credentials. For further advice and secret storage techniques, you may see
these article:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/ht
ml/SecNetch12.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconsecureadonetconnections.asp

Hope this help,

Luke Zhang
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)