2

I connect from classic ASP page to SQL SERVER 2008 R2 with following connection string

"Data Source=(local);Initial Catalog=my_db;Persist Security Info=True;User ID=my_user;Password=my_pass;"

but I get error

Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I verified that my_db exists!, so what is wrong in my connection string?

theateist
  • 13,879
  • 17
  • 69
  • 109

4 Answers4

1

The (local) name you've given for your data source is invalid. It should be an IP address, server name or server/instance name of your SQL installation.

UPDATE

If the SQL server is on the webserver you can use . to specify local. Here's an example connection string from one of my classic ASP projects:

"Provider=SQLNCLI10;Server=.;Database=my_db;Uid=user;Pwd=pass;"

Rory McCrossan
  • 331,213
  • 40
  • 305
  • 339
  • I run asp classic page on the same machine where SQL is installed. In ASP.NET I use (local) but in ASP Classic it doesn't work – theateist Nov 15 '11 at 10:34
  • In which case you can use `.` to specify a local SQL installation. I've updated my answer with an example. – Rory McCrossan Nov 15 '11 at 10:37
  • I tried it and now it says `Login failed for user 'user'`. I checked and user exist and mapped to 'my_db' database. what can be the problem now? – theateist Nov 15 '11 at 10:45
  • The connection string is now working in that case, the user credentials you supplied were incorrect. Have you checked them in the security settings of the database? – Rory McCrossan Nov 15 '11 at 10:51
  • Fixed it. The reason was 'An attempt to login using SQL authentication failed. Server is configured for Windows authentication only'. Thx – theateist Nov 15 '11 at 11:01
0

You need to specify an oledb provider to prevent it trying to default to ODBC (and failing)

Provider=SQLNCLI10;Data Source=(local);Initial Catalog=my_db;User ID=XXX;Password=YYY;

(You also need the 2008 SQL Client OleDb provider installed on the machine)

Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • I tried it and now it says Login failed for user 'XXX'. I checked and user exist and mapped to 'my_db' database. what can be the problem now? – theateist Nov 15 '11 at 10:48
  • Are you using a named instance? Look in the server log to see why the login request was denied – Alex K. Nov 15 '11 at 10:51
  • Fixed it. The reason was 'An attempt to login using SQL authentication failed. Server is configured for Windows authentication only'. Thx. Sorry, but I can't accept 2 answer and because Rory answered first so I'll accept his answer. – theateist Nov 15 '11 at 11:01
0

try to change (local) with : 127.0.0.1

Eystein Bye
  • 5,016
  • 2
  • 20
  • 18
0

Try:

Provider=SQLNCLI10;Server=(local);Database=my_db;Uid=my_user; Pwd=my_pass;

If in doubt, check connectionstrings.com.

CJM
  • 11,908
  • 20
  • 77
  • 115