Secure Connection string + Global.asa

Hi There

I have a connection string in my Global.asa file that looks something like this.

Session("connstring") = "driver={SQL Server};server=.;database=myDB;uid=sa;pwd=password"

I want to write the connection string so that the username and password is not in the string.
Also I dont want the web app to run using my SA acount.

Whats the minimum rights a sql account must have in order to run a web app.
Stanton_RouxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carl TawnSystems and Integration DeveloperCommented:
It depends.

The account basically just needs to be a standard users account with the read/write permissions to the appropriate tables and execute on any stored procs.
0
sybeCommented:
The username/password must be in the string. You can make it a variable, but then still it needs to be in it. You could create an ODBC-connection on the server, and refer to the name of the ODBC-connection - that's the only way I know to have username/password elsewhere.

Also I would like to recommend to use OLEDB:

PROVIDER=SQLOLEDB;DATA SOURCE=localhost;USER ID=sa;PASSWORD=;DATABASE=myDB;

For the permissions of the account: carl_tawn already answered that question
0
deighcCommented:
Another way is not to SQL authentication at all - use Windows authentication to the SQL Server instead.

The first step is to give the IUSR_<machine name> account login permissions to the SQL Server then allow it access to the database(s).

After that you need to change your connection string to:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<db name>;Data Source=<name or IP of SQL Server>

This way the user authentication to the SQL Server is done using Windows credentials and you never have to send a username and password with the connection string.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

deighcCommented:
> The first step is to give the IUSR_<machine name> account login
> permissions to the SQL Server then allow it access to the database(s).

... this assumes that your web app uses anonymous authentication.
0
kevp75Commented:
you could also do it setting up the machines DSN
0
Stanton_RouxAuthor Commented:
Is it not a security risk giving the iuser account acces to the DB
0
deighcCommented:
> Is it not a security risk giving the iuser account acces to the DB

You could argue that exposing your database to anybody anyhow is a security risk. But your database isn't much use if no one can access it.

SQL Server provides a very fine grained security model so you can easily limit access to the specific database(s) and database objects that each user is allowed to access.

I would say that, so long as you provide access only to the required databases, using Windows Integrated authentication via the IUSR account is much less of a security issue risk because this way you never send passwords over the wire.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.