Wednesday, March 28, 2012

This connections gonna kill me

This is a long one. (Question that is) an I have been working on this problem for a week now and still have no idea. There are no systems/database people here to help.

I have used the following string to succesfully connect to my SQL Server
database :

"Provider=SQLOLEDB.1;
Password=mypassword;
Persist Security Info=True;
User ID=VSdotNET;
Initial Catalog=ITPSCustomers;
Data Source=DELL\VSdotNET"

The problem is that I do not want SQL Server authentication so I opened up
SQL Server, removed the login VBdotNET, added it again and changed the user
authentication to Windows. I then changed the connection string to :

"Provider=SQLOLEDB.1;
Integrated Security=SSPI;
Persist Security Info=False;
Initial Catalog=ITPSCustomers;
Data Source=DELL\VSdotNET"

DELL\VSdotNET is a Login and has been given permission to access the
database ITPSCustomers.

Within the Database node for ITPSCustomers database DELL\VSdotNET has been
added as a user and has been given all permisions to the tables and views
within the database.

When I now run my web page I get the errors :

System.Data.OleDb.OleDbException: Cannot open database requested in login
'ITPSCustomers'. Login fails.
at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
at System.Data.OleDb.OleDbConnection.InitializeProvider()
at System.Data.OleDb.OleDbConnection.Open()
at DreamweaverCtrls.DataSet.DoInit() System.Exception:
The DefaultView was requested but no tables yet exist.
at DreamweaverCtrls.DataSet.get_DefaultView()

Can anyone help ?two things:

You should be using the SqlClient Namespace for all things related to data since you're using SQL Server (it's much faster)
Try this connection when using a SqlConnection

"Data Source=DELL\VSdotNET;
Trusted_Connection=True;
Initial Catalog=ITPSCustomers"

Hope that helps :)
Well the question is when you switched to windows authentication did you add the user unders security=>users in enterprise manager. Under the general tab beside name you get a browse button to pick a windows login to add and then you specify what database it can access under the database access tab. The SqlClient namespace is better for sql server (or msde) but it is unlikely to be the cause of the problem.

0 comments:

Post a Comment