Solved

System.Data.SqlClient.SqlException: Login failed for user '<domain>\NSIWS01$' error when trying to connect

Posted on 2008-06-09
8
1,205 Views
Last Modified: 2013-11-26
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.SqlException: Login failed for user '<domain>\NSIWS01$'.

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="DeviceIssuesConnectionString" connectionString="Data Source=NSSQL03-DEV;Initial Catalog=DeviceIssues;Integrated Security=True"
   providerName="System.Data.SqlClient" />

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
0
Comment
Question by:northstarneuro
  • 4
  • 4
8 Comments
 
LVL 8

Expert Comment

by:Yiogi
ID: 21747296
Remove integrated security and supply a username and password. Integrated security will always try to connect as ASP.NET user in a web application as that is the user essentially running it. Change your connection string to
"Data Source=NSSQL03-DEV;Initial Catalog=DeviceIssues;user id=username;password=password"
0
 

Author Comment

by:northstarneuro
ID: 21747380
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.SqlException: Login failed for user 'northstar\svc_SQL(NSSQL03-DEV)'. The user is not associated with a trusted SQL Server connection.

The connection string now looks like the following:

  <add name="DeviceIssuesConnectionString" connectionString="Data Source=NSSQL03-DEV;Initial Catalog=DeviceIssues;user id=svc_SQL(NSSQL03-DEV);password=<enter password here>"
   providerName="System.Data.SqlClient" />

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

0
 
LVL 8

Expert Comment

by:Yiogi
ID: 21747399
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).
0
 

Author Comment

by:northstarneuro
ID: 21747439
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-->Users.  I have allowed the admin group to be the database owner.
--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
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 8

Expert Comment

by:Yiogi
ID: 21747487
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" password="password"/>
</system.web>
0
 

Author Comment

by:northstarneuro
ID: 21747570
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
0
 

Author Comment

by:northstarneuro
ID: 21747650
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
0
 
LVL 8

Accepted Solution

by:
Yiogi earned 500 total points
ID: 21756004
Ok for your last post first. Go to the login of that user and uncheck the checkbox "enforce password policy". This will set it so a) user doesn't have to change password on next logon, and b) password won't expire.

For the before last post if this is an internal application no you don't put the username and password. It will impersonate the person trying to access it if you set identity impersonate to true. You'll just have to add the domain users as sql server users and set their permissions.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now