.NET MS SQL Connection string

[david7777]

I am trying to connect to my MS SQL database, but i am getting a runtime error which i think is related to security or authorization...

I am using the connection string "server=localhost;Trusted_Connection=true;database=purplepassportDB", with a SqlConnection. When it gets to the point of calling the myConnection.Open() method, the error is thrown.

Im new to MS SQL, so i dont really know where to start...

My error message i get is:

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'purplepassportDB'. Login fails. Login failed for user 'DAVID\ASPNET'.

Any suggestions?

[oracleguy]

Are you supplying a username and password in your connection string?

[david7777]

No - i am using the exact connection string supplied above...

"server=localhost;Trusted_Connection=true;database=purplepassportDB"

The database has windows NT integrated security enabled.

[Roy Sinclair]

Forget using a connection string and try it this way:


Set oConn = Server.CreateObject("ADODB.Connection")
With oConn
.Provider = "SQLOLEDB"
.Properties("Data Source").Value = "localhost"
.Properties("Initial Catalog").Value = "purplepassportDB"
.Properties("Integrated Security").Value = "SSPI"
.Properties("Persist Security Info").Value = False
End With
oConn.Open

[david7777]

ok, but isnt that detracting from .NET, and going back to the old classic asp way?

[allida77]

Did you give rights to the ASPNet account on your SQL server? Go into your security accounts on your sql server and add the ASPNET account with permissions to this table.

You definitely want to use the SQLClient class for this.

[david7777]

Well - i thought i might have to do something like that, but i dont have SQL server or anything... I am running MSDE and using VS.net to edit the database. Can i edit the security accounts using VS?

Also - Would i the SQLClient class, or the SqlConnection class?
At the moment i am using the SqlConnection class.

I am using only stored procedures, so what is best?

Thanx

[allida77]

Do a search on the www.asp.net forums for msde and security. I believe you set the permissions command line. I have yet to use the msde.

You are correct in using the SQLConnection Class. I should of said use the SQLClient Namespace in my earlier post.

[oog]

You need to grant your ASPNET user access to your database. For SQL Server, if you don't have the admin tools, you could run a script like this:

-- Create Server logins
USE Master
go
EXEC sp_grantlogin 'domain\user'
go

-- Grant access to database
USE dbname
GO
EXEC sp_grantdbaccess 'domain\user', 'user'
GO

That should work in giving your ASPNET account the right access.