northstarneuro
asked on
System.Data.SqlClient.SqlException: Login failed for user '<domain>\NSIWS01$' error when trying to connect
I have created a very simple web application with Visual Studio 2008. During development I created a database connection with a service account that my login has access to the database. But when I deploy the web application to a separate IIS server I get the following error message:
System.Data.SqlClient.SqlE
I know that that the connection is not being made and that it is trying to connect using the server name of the IIS server (NSIWS01). What I do not know is how to configure SQL Server 2005 so that I can use one service account that will connect to the database when it is deployed to a separate IIS server and in addition can be used when I am doing devleopment work on my workstation using Visual Studio 2008. IN the web.config file my connection string looks like the following:
<add name="DeviceIssuesConnecti
providerName="System.Data.
The datasource is the name of the server and the Catalog seems to be the name of the table within the database. The rest is not so sure about.
If anyone knows how to configure this so that I can get it working it would be greatly appreciated. I am new to both Visual Studio as well as SQL Server 2005.
Thank you,
Randy
System.Data.SqlClient.SqlE
I know that that the connection is not being made and that it is trying to connect using the server name of the IIS server (NSIWS01). What I do not know is how to configure SQL Server 2005 so that I can use one service account that will connect to the database when it is deployed to a separate IIS server and in addition can be used when I am doing devleopment work on my workstation using Visual Studio 2008. IN the web.config file my connection string looks like the following:
<add name="DeviceIssuesConnecti
providerName="System.Data.
The datasource is the name of the server and the Catalog seems to be the name of the table within the database. The rest is not so sure about.
If anyone knows how to configure this so that I can get it working it would be greatly appreciated. I am new to both Visual Studio as well as SQL Server 2005.
Thank you,
Randy
ASKER
I have removed the integrated security and I now get the following error message when I try to run the web application from Visual Studio:
System.Data.SqlClient.SqlE
The connection string now looks like the following:
<add name="DeviceIssuesConnecti
providerName="System.Data.
What I also don't understand is that I have set up SQL Server for Windows Authentication so why would I have to hardcode the username and password into the application?
Again your help is appreciated.
Thank you,
Randy
System.Data.SqlClient.SqlE
The connection string now looks like the following:
<add name="DeviceIssuesConnecti
providerName="System.Data.
What I also don't understand is that I have set up SQL Server for Windows Authentication so why would I have to hardcode the username and password into the application?
Again your help is appreciated.
Thank you,
Randy
This doesn't look like an sql username to me "svc_SQL(NSSQL03-DEV)". You are supposed to use an SQL username and password not a windows one. You have to enable mixed authentication mode on your sql server and create an sql login with the username and password you want to use. You cannot login using domain or windows credentials on a web app (Well you can if its within your internal domain and you associate the domain names with sql server logins like Microsoft CRM for example but thats different).
ASKER
OK, here is what I have and please let me know where I might be going wrong.
--I have a service account that owns the whole database and it is svc_SQL(NSSQL03-DEV).
--I have an admin group created in AD that has my account in it and then this group has been applied to the Security --> Logins portion of SQL Server 2005 and then also so the database security DeviceIssues-->Security-->
--When I installed SQL Server 2005 I enabled it for Window Authentication and not both
--In Visual Studio when I create the database connection it is supposed to be using Windows authentication as well (this works since it is using my windows credentials and I am part of the admin group that owns the database). But when the application is deployed to IIS then I am not logged in and the windows authentication fails.
Your help is appeciated.
Thank you,
Randy
--I have a service account that owns the whole database and it is svc_SQL(NSSQL03-DEV).
--I have an admin group created in AD that has my account in it and then this group has been applied to the Security --> Logins portion of SQL Server 2005 and then also so the database security DeviceIssues-->Security-->
--When I installed SQL Server 2005 I enabled it for Window Authentication and not both
--In Visual Studio when I create the database connection it is supposed to be using Windows authentication as well (this works since it is using my windows credentials and I am part of the admin group that owns the database). But when the application is deployed to IIS then I am not logged in and the windows authentication fails.
Your help is appeciated.
Thank you,
Randy
When you deploy the application it will try to login as ASP.NET user. This is a machine user and not a domain user.
You can do either of the following 2:
1. Go to your sql server management studio, right click on the instance and go to properties. Go to the security tab and select "SQL Server & Windows Authentication Mode". then go to security, logins and create the login name and password you want. Also give it permissions to the database you want or assign it a role. You do not need to create anything in your domain since users running the application will not be users from within your domain but web users.
2. You could also change your web config to impersonate a certain user (like yourself for example). You'd need to add this setting to your web.config (CASE SENSITIVE) under system.web
<system.web>
<identity impersonate="true" userName="DOMAIN\username"
</system.web>
You can do either of the following 2:
1. Go to your sql server management studio, right click on the instance and go to properties. Go to the security tab and select "SQL Server & Windows Authentication Mode". then go to security, logins and create the login name and password you want. Also give it permissions to the database you want or assign it a role. You do not need to create anything in your domain since users running the application will not be users from within your domain but web users.
2. You could also change your web config to impersonate a certain user (like yourself for example). You'd need to add this setting to your web.config (CASE SENSITIVE) under system.web
<system.web>
<identity impersonate="true" userName="DOMAIN\username"
</system.web>
ASKER
In your second option do you need so actually specify a username and a password or will it pull it from the user that is currently trying to access the database?
And I don't know if this changes anything, but the users will be people from our domain. This is an internal application that we are rebuilding so that it can be deployed on the web. This was the reaon we wanted windows authentication so that we would not have to worry about the application being affected with a password change. Also if I do option one how do I do development work if I can not use windows authentication to connect to the database?
Thank you,
Randy
And I don't know if this changes anything, but the users will be people from our domain. This is an internal application that we are rebuilding so that it can be deployed on the web. This was the reaon we wanted windows authentication so that we would not have to worry about the application being affected with a password change. Also if I do option one how do I do development work if I can not use windows authentication to connect to the database?
Thank you,
Randy
ASKER
Also I did what you mentioned in option one. I create a new user on the SQL Server named Device Issues. I enabled SQL Server & Windows Authentication Mode and gave access to this new user (as the database owner.
I now go into Visual Studio and when I see the database connection I right click and go 'Modify Connection'. I change it to SQL Authentication and enter in the new DeviceIssues account information. I get the message that, "Login failed for user 'DeviceIssues'. Reason: The password of the account must be changed.
I then go to the server name option and when I do it refreshes and when it is done I am no longer able to see my DEV SQL Server (NSSQL03-DEV).
Thank you,
Randy
I now go into Visual Studio and when I see the database connection I right click and go 'Modify Connection'. I change it to SQL Authentication and enter in the new DeviceIssues account information. I get the message that, "Login failed for user 'DeviceIssues'. Reason: The password of the account must be changed.
I then go to the server name option and when I do it refreshes and when it is done I am no longer able to see my DEV SQL Server (NSSQL03-DEV).
Thank you,
Randy
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
"Data Source=NSSQL03-DEV;Initial