We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Secure Connection string + Global.asa

Stanton_Roux
Stanton_Roux asked
on
Medium Priority
637 Views
Last Modified: 2008-03-06
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.
Comment
Watch Question

Carl TawnSenior Systems and Integration Developer
CERTIFIED EXPERT

Commented:
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.

Commented:
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
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
> 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.

Commented:
you could also do it setting up the machines DSN

Author

Commented:
Is it not a security risk giving the iuser account acces to the DB

Commented:
> 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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.