In MSDN, it says that it is recommended to use windows authentication to connect to SQL Server rather than use mixed authentication.
I create user delta\sqluser on windows OS, and I specify in my webform ASP.NET script below :
protected System.Web.UI.WebControls.Label Label1;
private string _connString = @."data source=delta\sql2000;initial catalog=northwind;integrated security=false;user id=delta\sqluser";
/*
comment : I login to my windows as delta\koronx, and I want to every user (including me), connected to sql server through IIS, will be identified as delta\sqluser not as user's login (impersonate)
*/
private void Page_Load(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection(_connString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select suser_sname()";
conn.Open();
string userName = cmd.ExecuteScalar() as string;
conn.Close();
conn.Close();
Label1.Text = userName;
}
at web.config, I add :
<identity impersonate="false" userName="delta\sqluser" password="" />
at IIS webApplication1's properties, tab "Directory Security", at "Authentication and access control" section, I checked "enable anonymous access" with user : DELTA\IUSR_DELTA and checked "Integrated Windows Authentication",
at query analyzer, I login as "sa" and execute script below :
exec sp_grantdbaccess 'delta\sqluser','northwind'
when I run the ASP.NET script, error at conn.Open(); with error message :
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
What should I do so that IIS login to SQL Server as user delta\sqluser not as "NT AUTHORITY\NETWORK SERVICE" ?
Regards,
Koronx
Your connection string is incorrect. You specify using SQL Authentication (integrated security = false) but what you want is integrated security. However, the connection string for integrated security does not allow you to impersonate a user. You should look for help on configuring your application to impersonate a specific account on an ASP.NET forum at: http://forums.asp.net/.
Thanks
Laurentiu
ystem.Runtime.InteropServices.COMException (0x800A0E7A): Provider cannot be found. It may not be properly installed.