Connecting to Microsoft SQL Server from Visual Basic

Question

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?

Solution

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.

• Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication

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:

• How To: Connect to SQL Server Using SQL Authentication in ASP.NET 2.0