| Author |
Topic |
|
thomwcox
Starting Member
5 Posts |
 Posted - 2007-08-31 : 08:11:59
|
| Hi All,I'm having trouble getting at SQL Server 2005 Express via ASP code.I'm using the following connection string in my ASP code:Set objConn = Server.CreateObject("ADODB.Connection")objConn = "Provider = SQLNCLI;" & _"Data Source = .\SQLEXPRESS;" & _"Initial Catalog = myDB;" & _"User ID = DB_reader;" & _"Password = a#koddkobn5;"I keep getting the error:Microsoft SQL Native Client (0x80004005)Login failed for user 'DB_reader'. The user is not associated with a trusted SQL Server connectionI have made sure that the user exists in SQLEXPRESS and that server Security is set to SQL Server and Windows Authentication mode.Can anyone think of anything I'm missing or that I should try?We've lost our SQL Server admin and I'm trying to develop locally using Express while we hunt for a new admin.Thanks so much for any assistance you have time to offer.Very best,Thom Cox |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-31 : 08:25:09
|
Have you added 'DB_reader' as user in the database?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
thomwcox
Starting Member
5 Posts |
Posted - 2007-08-31 : 08:30:58
|
| Hi Peso,Yes, I added DB_reader by way of a new query with the command:CREATE LOGIN DB_reader WITH PASSWORD = a#koddkobn5Is there anything additionally I have to do or should that have covered it? Thanks!- Thom |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-31 : 08:35:59
|
Yes, you must grant permissions to the db_reader for the tables and objects you want access for.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
thomwcox
Starting Member
5 Posts |
Posted - 2007-08-31 : 08:44:33
|
| Sorry, should have mentioned - yes, I did this:Databases --> Security --> User MappingI chose the appropriate database and gave db_datawriter and db_datareader access.I also tried giving the global role of db_creator in Server Roles to no avail. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-08-31 : 08:53:15
|
| Did you try what I suggested?Also -- I am not sure if all those extra spaces in your connect string might be causing a problem as well. Try removing the spaces before and after the = symbols.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
thomwcox
Starting Member
5 Posts |
Posted - 2007-08-31 : 08:59:34
|
| Thanks JSmith and Peso,I've actually tried quite a variety of Conn strings but keep getting the same error saying my Uid is not a trusted SQL connection which I don't userstand because I"m trying to auth via SQL Server Authentication.I created a new user from scratch and tried all the following Conn's:Set objConn = Server.CreateObject("ADODB.Connection")'objConn = "Provider=SQLNCLI;Server=.\SQLEXPRESS;Database=tlat_data_dev_new;Uid=aliceCooper;Pwd=sickThings;"objConn = "Driver={SQL Native Client};Server=.\SQLEXPRESS;Database=tlat_data_dev_new;Uid=aliceCooper;Pwd=sickThings;"'objConn = "Provider = SQLNCLI;" & _' "Data Source = .\SQLEXPRESS;" & _' "Initial Catalog = tlat_data_dev_new;" & _' "User ID = aliceCooper;" & _' "Password = sickThings;" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-31 : 09:01:10
|
www.connectionstrings.com
E 12°55'05.25"N 56°04'39.16" |
|
|
|
thomwcox
Starting Member
5 Posts |
Posted - 2007-08-31 : 09:45:22
|
| Issue Resolved:Guys thanks so much for all your help. I feel like a total dweeb. It came down to my not having restarted the server after I changed the auth mode from Windows Auth to Mixed Mode. I thought Disconnect/Connect was restarting the server. I did a net stop/start and I'm up and running.Thanks again,- twc |
|
|
|
|
connecting to SQL Server Express via ASP (classic)