sql server connection

[Nikhil Patel]

Hi all,
I am using windows authentication to create a sql connection using
following connection string stored in web.config.
<add key="GoldmineConnectString"

value="server=(local);initial catalog=pubs;Integrated Security=SSPI" />

But when I run the application, the application is trying to connect to the
database as MACHINENAME/ASPNET user. I would like it to use a special
windows account I have created for my application called MYAPPUSER. How can
I make the application to use this windows account to connect to the sql
server?

Thanks.

Nikhil

 

[Steve C. Orr [MVP, MCSD]]

You could use impersonation to have ASP.NET run under the new windows
account you've created.

For example, you can add a line similar to this to your web.config file:
<identity impersonate="true" userName="domain\MyAppUser">
password="password"/>

Here's more info on impersonation:
http://msdn.microsoft.com/library/d...-us/cpguide/html/cpconaspnetimpersonation.asp

 

[Scott Allen]

HI Nikhil:

Two options available are:

1) Configure impersonation in web.config with the account and password
See:
http://msdn.microsoft.com/library/d...-us/cpguide/html/cpconaspnetimpersonation.asp

2) Change the ASP.NET process model in machine.config so the entire
worker process runs as your MYAPPUSER account.
See:
http://msdn.microsoft.com/library/d...n-us/vsent7/html/vxconApplicationIdentity.asp


HTH,

 

[Nikhil Patel]

Hi Steve,
Thanks for your reply. The reason why I am using windows authentication
is that I don't want to write password in the web.config. So your method
will not work in this case. I don't know too much about ASP.Net Security and
Data Access Security. I was reading MSDN regarding these topics and I found
the following section:

Avoid impersonation in the middle tier: Windows authentication requires a

Windows account for database access. Although it might seem logical to use

impersonation in the middle tier, avoid doing so because it defeats
connection

pooling and has a severe impact on application scalability.

To address this problem, consider impersonating a limited number of Windows

accounts (rather than the authenticated principal) with each account
representing

a particular role.

For example, you can use this approach:

1. Create two Windows accounts, one for read operations and one for write

operations. (Or, you might want separate accounts to mirror
applicationspecific

roles. For example, you might want to use one account for Internet

users and another for internal operators and/or administrators.)

2. Map each account to a SQL Server database role, and establish the
necessary

database permissions for each role.

3. Use application logic in your data access layer to determine which
Windows

account to impersonate before you perform a database operation.

Note: Each account must be a domain account with Internet Information
Services (IIS) and

SQL Server in the same domain or in trusted domains. Or, you can create
matching accounts

(with the same name and password) on each computer.


So I created a special windows account and gave it database permissions to
execute stored procedures. I am not sure whether I need this account or
should I just give database permissions to ASPNET account? I couldn't find
any practical example on what accounts I need to create,etc,etc. What are
the best practices?

Thanks.
Nikhil

 

[Patrice]

You can encrypt this.

See :
http://support.microsoft.com/default.aspx?scid=kb;en-us;329290

Patrice