Classic ASP to SQL 2008 Connection Problems

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Classic ASP to SQL 2008 Connection Problems

Author

Topic

Morledge
Starting Member

5 Posts

Posted - 2008-11-19 : 10:37:16

I am currently migrating one of my clients sites to a windows server 2008 and SQL 2008 setup, but I am having massive problems with connecting to the database from the site.

I have restored the database from a SQL 2k backup into the SQL 2008 server, I have setup the user correctly and can login as that user in management studio fine. I have copied over the site .asp files which load fine when there is no database access. But when i try to access the database it fails with "Login Failed for user......".

I have reset the passwords, created new users, changed the connectionstring from OLEDB to SQL Native Client and back again but keep getting errors. I have even setup a dummy database and user and still have the same problem.

Does anyone know of a reason why this could be happening? Is there a setting in SQL or windows that I am missing?

I have been at this for hours and would really appreciate any ideas.

UPDATE 1 : If I put the wrong login details in the connection string I get the error on "conn.open" but if I put in the correct login details I get the error on "cmd.activeconnection = conn". Not sure if that helps.

UPDATE 2 : I did try setting up a UDL on the server which connected so I copied the connection string and now it returns an error "Object required: 'Provider=SQLOLEDB.1;' " on line "cmd.activeconnection = conn"

The connection string is

"Provider=SQLOLEDB.1;Password=test;Persist Security Info=True;User ID=TestLogin;Initial Catalog=TestDB;Data Source=ServerName"

Thanks
Chris

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 10:39:22

The NATIVE SQL provider name is SQLNCLI10, not SQLOLEDB.1
See www.connectionstrings.com

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

E 12°55'05.63"
N 56°04'39.26"

Morledge
Starting Member

5 Posts

Posted - 2008-11-19 : 10:46:59

Thanks Peso,

I have already tried that, but I tried again and it comes back with

"Object required: 'Provider=SQLNCLI10.1'"

My current connection string is:

"Provider=SQLNCLI10;Server=DS-47500;Database=TestDB;Uid=TestLogin;Pwd=test;"

EDIT : Just to clarify, this does not error on my "conn.open" line but my "cmd.activeconnection = conn" line.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 10:49:57

Try

"Provider=SQLNCLI10;Server=[DS-47500];Database=TestDB;Uid=TestLogin;Pwd=test;"

E 12°55'05.63"
N 56°04'39.26"

Morledge
Starting Member

5 Posts

Posted - 2008-11-19 : 10:56:20

I am now getting

"Named Pipes Provider: Could not open a connection to SQL Server [53]."

on the "conn.open" line

EDIT : I just tried the IP address as the server and I am back to my Object Required error.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 11:10:46

The named pipes error is the "rigth" error for the moment, because it shows it tried to connect to the server.
Have you opened up named pipes as a mean of communication? Is port 1433 opened?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 11:13:14

Also see this http://www.connectionstrings.com/article.aspx?article=howtodefinewichnetworkprotocoltouse
for different types of libraries.

E 12°55'05.63"
N 56°04'39.26"

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-11-19 : 12:34:42

quote:
Originally posted by Morledge

I am now getting

"Named Pipes Provider: Could not open a connection to SQL Server [53]."

on the "conn.open" line

EDIT : I just tried the IP address as the server and I am back to my Object Required error.

You shouldnt be using named pipes, or if you must, use named pipes and IP.

Change Named pipes to IP and try again

Morledge
Starting Member

5 Posts

Posted - 2008-11-19 : 12:39:11

Hi Peso, thanks for the help you have provided so far.

I looked into the Named Pipes error I got and realised that the problem was with Named Pipes not being enabled in the SQL config, I have enabled this and now it can connect to the server but I get the same "Object required: 'Provider=SQLNCLI10.1'"
error.

So now my SQL connection string reads

"Provider=SQLNCLI10;Server=DS-47500;Database=TestDB;Uid=TestLogin;Pwd=test;Network=dbmssocn;"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources