Connecting Sql Server 2005 from ASP? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Old Forums

CLOSED - SQL Server 2005/Yukon

Connecting Sql Server 2005 from ASP?

Author

Topic

Mits
Starting Member

48 Posts

Posted - 2006-06-16 : 10:25:59

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

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-06-16 : 11:39:04

www.connectionstrings.com

Mits
Starting Member

48 Posts

Posted - 2006-06-16 : 12:27:42

thanks afrika
I have checked that site already.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-06-16 : 12:48:56

Glad it helped

Mits
Starting Member

48 Posts

Posted - 2006-06-17 : 07:18:35

Hi afrika,
sorry, but the problem is not solved.

I downloaded SQL Native Client and installed on the web server.

I looked in SAC Tool and "Local and Remote Connection" and "Using Both TCP/IP and Named Pipes" options are selected. I have checked SQL
Browser service is running.

Now when i use this string

"Provider=SQLNCLI;Server=127.0.0.1;Database=DBName;UID=UserName;PWD=UserPassword;"

i get

Microsoft SQL Native Client error '80040e4d'

Login failed for user 'UserName'.

I have created this user and this user has proper permission to access
the database i need.
Is there any special permissions that need?

Mits

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-06-17 : 18:36:44

There could be so many reasons.

but

1. Are you using SQLServer authentication or Windows Authentication? My guess is that you're using windows authentication. try switching to SQL authentication and giving it a valid uid and password.

2. Do not used namepipes, always use TCP/IP

3. 127.0.0.1 Is definitely a wrong IP address, its used for loop back testing and not a real IP. Use your correct db name.

4. Verify your parameters are all correct. Database, login ID, password etc

hope this sorts it out

Afrika

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-06-17 : 18:38:20

Also see
http://support.microsoft.com/default.aspx/kb/222828

Mits
Starting Member

48 Posts

Posted - 2006-06-20 : 07:57:01

Good News guys got it all working.
I really appreciate all the effort and time put in by afrika .

These are the steps i did, and i dont 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 <Instance Name>
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources