Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-06-09
8
Medium Priority
?
1,381 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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 2000 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

715 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