468,119 Members | 1,924 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,119 developers. It's quick & easy.

VB2005 - Secure Access to SQL Server through Application Only

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
Apr 9 '06 #1
6 1435
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" <br**********@community.nospam> wrote in message
news:e6*************@TK2MSFTNGP05.phx.gbl...
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

Apr 9 '06 #2
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

Vijay wrote:
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

Apr 9 '06 #3
Have a look at the String class for methods that provide mainuplation
functionality.

Also have a look at the various Encryption classes.
"Matt" <br**********@community.nospam> wrote in message
news:OR**************@TK2MSFTNGP04.phx.gbl...
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

Vijay wrote:
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

Apr 9 '06 #4
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

Stephany Young wrote:
Have a look at the String class for methods that provide mainuplation
functionality.

Also have a look at the various Encryption classes.
"Matt" <br**********@community.nospam> wrote in message
news:OR**************@TK2MSFTNGP04.phx.gbl...
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

Vijay wrote:
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


Apr 13 '06 #5
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?

Matt wrote:
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

Stephany Young wrote:
Have a look at the String class for methods that provide mainuplation
functionality.

Also have a look at the various Encryption classes.
"Matt" <br**********@community.nospam> wrote in message
news:OR**************@TK2MSFTNGP04.phx.gbl...
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

Vijay wrote:
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


Apr 13 '06 #6
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/de...us/dnnetsec/ht
ml/SecNetch12.asp

http://msdn.microsoft.com/library/de...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.)

Apr 14 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by ojorus | last post: by
6 posts views Thread by Sarah Tanembaum | last post: by
15 posts views Thread by Aalaan | last post: by
6 posts views Thread by =?Utf-8?B?Q3JhaWc=?= | last post: by
1 post views Thread by Annonymous Coward | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.