June 16, 2006 at 8:24 am
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
June 18, 2006 at 6:33 am
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
June 19, 2006 at 2:42 am
I am failing to understand how would uninstalling and reinstalling SS2005 will solve the problem?
Mits
June 19, 2006 at 4:03 am
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
June 20, 2006 at 5:57 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply