I am attempting to connect to Microsoft SQL Server 2008 R2 from within Microsoft Visual Studio 2010. My connection string is as follows:
connection = New SqlConnection("Server=localhost;Database=greenapplication;User ID=WindowsID;Password=WindowsPassword")
I keep getting this exception: System.Data.SqlClient.SqlException:
Login failed for user 'WindowsID'.
What am I doing wrong? Are there certain permissions that I have to set up?
If you are trying to use integrated security, your connection string should be:
"Server=localhost;Database=greenapplication;Trusted_Connection=True"
You then manage permissions through roles and windows groups. (You can grant individual users permissions, but this is not the recommended way to manage database access).
UPDATE: You neglected to mention in original question that this is a web application. The usual way to handle this is to create an app. pool running under a known identity (say NETWORK SERVICE), and then give that identity the necessary permissions on the database.
Here's an example:
-- Create a SQL Server login for the Network Service account
sp_grantlogin 'NT AUTHORITY\Network Service'
-- Grant the login access to database
USE MyDB
GO
sp_grantdbaccess 'NT AUTHORITY\Network Service', 'Network Service'
-- Add user to read only database role
USE MyDB
GO
sp_addrolemember 'db_datareader', 'Network Service'
If you insist on using the less secure method of passing of username and password in connection string, use SQL Server logon:
since you said "WindowsPassword", I think you're probably supposed to be using this connection string:
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
I'd also advise visiting this site:
You can try this.
"Provider=SQLOLEDB;Data Source=" & SQLSERVER NAME & "; Initial Catalog=" & Dbname & ";Network=DBMSSOCN;" & _
"User Id=" & UID & ";" & _
"Password=" & Pwd & ";"
