Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1653
  • Last Modified:

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.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
northstarneuro
Asked:
northstarneuro
  • 4
  • 4
1 Solution
 
YiogiCommented:
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
 
northstarneuroAuthor Commented:
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
 
YiogiCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
northstarneuroAuthor Commented:
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
 
YiogiCommented:
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
 
northstarneuroAuthor Commented:
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
 
northstarneuroAuthor Commented:
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
 
YiogiCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now