Connecting Sql Server 2005 from ASP?

Question

Connecting Sql Server 2005 from ASP?

CMitesh

Say Hey Kid

Points: 674
More actions
June 16, 2006 at 8:24 am

#168846

Hi
We are planning to move over to SQL Server 2005 in near future.
At the moment Website is on a seperate server then the Database.
OS for both the server is Window 2003 and currently our data is on SQL Server 2000(on which everything works fine).
Part of the testing process we tried to connect our website on SQL 2005 and it does not work at all.
I get this
----------------------------------------------------------------
ADODB.Connection error '800a0e7a'
Provider cannot be found. It may not be properly installed.
When i use following connection string
"Provider=SQLNCLI;Server=127.0.0.1;Database=dbName;UID=UserName;PWD=UserPassword;"
(Above string has fixed problem for few people- googled it, but not for me.)
----------------------------------------------------------------
----------------------------------------------------------------
This is the message i get for all the following connectiong string
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
1) - "Provider=SQLOLEDB.1;Password=UserPassword;Persist Security Info=True;User ID=UserName;Initial Catalog=ABC;Data Source=XXX.XX.X.XX,1433"
2) - "Provider=sqloledb;Data Source=XXX.XX.X.XX,1433;Network Library=DBMSSOCN;Initial Catalog=ABC;User ID=UserName;Password=UserPassword"
3) - "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=UserName;Initial Catalog=ABC;Data Source = ServerName\SQLInstance"
----------------------------------------------------------------
I tried connecting to SQL Server 2005 through our system developed in VB.Net 2003 and it works fine and here is the Connection String :
"Data Source=ServerName\SQLInstance;Initial Catalog=ABC;Persist Security Info=False;user id=UserName;password=UserPassword"
I would really appreciate any help.
Mits

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

BR Das SSC Enthusiast Points: 182 More actions · Answer 1

I had a similar problem. I could even connect using the same connection string via ASP.NET. But from ASP I was getting the same errors as you list. The only thing that eventually worked for me was completely uninstalling SQL Server 2005 and reinstalling.

Good Luck!

BRDas

CMitesh Say Hey Kid Points: 674 More actions · Answer 2

I am failing to understand how would uninstalling and reinstalling SS2005 will solve the problem?

Mits

BR Das SSC Enthusiast Points: 182 More actions · Answer 3

Beats me why it worked! The problem was not on the webserver since the same type of connection worked previously with SS2000. There was nothing wrong with the connection string, since I tested it with the data link tester (udl). The connection string also worked with ASP.NET.

I also had been having a problem where SS2005 would not accept any SQLServer logins, only Windows logins, and could not find any solution to that.

So as a last resort I went with the uninstall and reinstall. I had first installed SQLServer Express, then upgraded, then I reorganized my hard drives and had to change the physical locations of the database files. All the other features were working except for these two related to logins. So I figured that SS2005Express was a fairly new program and there might be some bug in the upgrading process that had not been discovered yet. And the complete uninstall with reinstall solved them both.

It could be some coincidence, I guess. I'll leave that to the experts to figure out.

Thanks.

BRDas

CMitesh Say Hey Kid Points: 674 More actions · Answer 4

Good News guys got it all working.

These are the steps i did, and i dont know but it might help anyone out there.

Our Webserver is outside DMZ on our network. It can only communicate through secured channels on our linux firewall. I have got a named Instance of SQL Server 2005(Enterprise Edition)

1) I punched a hole through our firewall that will allow communication

between our Webserver and DB server through specific Port.

2) I Installed SQL Native Client on Webserver.

3) In SSCM(SQL Server Configuration Manager) Tool

On Console Pane, Click Protocols for

Double Click TCP/IP

Listen To All ---> No

For IP1

Active = Yes

Enabled = Yes

IP Address = IP of the DB Server

TCP = Blank/Nothing

TCP Port = 1433( or any port number but it has to be the same that

you have defined in firewall)

Save Everything and Re-start the SQL Server (Instance Name) Service.

This is the example of connection string that worked

Provider=SQLNCLI.1;Persist Security Info=False;User

ID=UserNamer;Password=UserPassword;Initial Catalog=NameofDatabase;Data

Source=DBServerName

Mits