Connecting Sql Server 2005 from ASP?

**Erland Sommarskog** · Jun 16 '06, 09:45 PM

Re: Connecting Sql Server 2005 from ASP?

(MVChauhan@goog lemail.com) writes:[color=blue]
> 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.Connectio n error '800a0e7a'
> Provider cannot be found. It may not be properly installed.
>
> When i use following connection string
> "Provider=SQLNC LI;Server=127.0 .0.1;[/color]

Did you install SQL Native Client on the web server? SQL Native Client
is a DLL that contains versions of both the ODBC SQL Server Driver
and the SQL Server OLE DB Provider that has been enhanced with support
for SQL 2005.

SQL Native Client is freely distributable.
[color=blue]
> 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.[/color]

Note that by default some editions of SQL 2005 by default only permits
local connections. You can modify this in the Surface Area Configuration
Tool.

You may also have to encure that the SQL Browser service is running.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**MVChauhan@googlemail.com** · Jun 17 '06, 10:25 AM

Re: Connecting Sql Server 2005 from ASP?

Hi Erland
Thanks for your reply.
I still cant conntect to SQL Server.

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=SQLNC LI;Server=127.0 .0.1;Database=D BName;UID=UserN ame;PWD=UserPas sword;"

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? This is driving me crazy.

Mits

You may also have to encure that the SQL Browser service is running.

Erland Sommarskog wrote:
[color=blue]
> (MVChauhan@goog lemail.com) writes:[color=green]
> > 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.Connectio n error '800a0e7a'
> > Provider cannot be found. It may not be properly installed.
> >
> > When i use following connection string
> > "Provider=SQLNC LI;Server=127.0 .0.1;[/color]
>
> Did you install SQL Native Client on the web server? SQL Native Client
> is a DLL that contains versions of both the ODBC SQL Server Driver
> and the SQL Server OLE DB Provider that has been enhanced with support
> for SQL 2005.
>
> SQL Native Client is freely distributable.
>[color=green]
> > 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.[/color]
>
> Note that by default some editions of SQL 2005 by default only permits
> local connections. You can modify this in the Surface Area Configuration
> Tool.
>
> You may also have to encure that the SQL Browser service is running.
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]

**pb648174** · Jun 17 '06, 11:15 AM

Re: Connecting Sql Server 2005 from ASP?

The SQL 2005 password is case sensitive while 2000 wasn't, so code that
works on SQL 2000 for logging in may not work on 2005.

MVChauhan@googl email.com wrote:[color=blue]
> Hi Erland
> Thanks for your reply.
> I still cant conntect to SQL Server.
>
> 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=SQLNC LI;Server=127.0 .0.1;Database=D BName;UID=UserN ame;PWD=UserPas sword;"
>
> 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? This is driving me crazy.
>
>
> Mits
>
>
>
>
>
>
>
> You may also have to encure that the SQL Browser service is running.
>
> Erland Sommarskog wrote:
>[color=green]
> > (MVChauhan@goog lemail.com) writes:[color=darkred]
> > > 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.Connectio n error '800a0e7a'
> > > Provider cannot be found. It may not be properly installed.
> > >
> > > When i use following connection string
> > > "Provider=SQLNC LI;Server=127.0 .0.1;[/color]
> >
> > Did you install SQL Native Client on the web server? SQL Native Client
> > is a DLL that contains versions of both the ODBC SQL Server Driver
> > and the SQL Server OLE DB Provider that has been enhanced with support
> > for SQL 2005.
> >
> > SQL Native Client is freely distributable.
> >[color=darkred]
> > > 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.[/color]
> >
> > Note that by default some editions of SQL 2005 by default only permits
> > local connections. You can modify this in the Surface Area Configuration
> > Tool.
> >
> > You may also have to encure that the SQL Browser service is running.
> >
> >
> >
> >
> > --
> > Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
> >
> > Books Online for SQL Server 2005 at
> > http://www.microsoft.com/technet/pro...ads/books.mspx
> > Books Online for SQL Server 2000 at
> > http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color][/color]

**MVChauhan@googlemail.com** · Jun 17 '06, 12:25 PM

Re: Connecting Sql Server 2005 from ASP?

I know there is no issue with Password.
I just realised one thing i dont know if it matters..
Between our webserver there is a firewall, do we need to punch a hole
that will allow communication between our webserver and DB Server.

Mits

pb648174 wrote:
[color=blue]
> The SQL 2005 password is case sensitive while 2000 wasn't, so code that
> works on SQL 2000 for logging in may not work on 2005.
>
> MVChauhan@googl email.com wrote:[color=green]
> > Hi Erland
> > Thanks for your reply.
> > I still cant conntect to SQL Server.
> >
> > 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=SQLNC LI;Server=127.0 .0.1;Database=D BName;UID=UserN ame;PWD=UserPas sword;"
> >
> > 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? This is driving me crazy.
> >
> >
> > Mits
> >
> >
> >
> >
> >
> >
> >
> > You may also have to encure that the SQL Browser service is running.
> >
> > Erland Sommarskog wrote:
> >[color=darkred]
> > > (MVChauhan@goog lemail.com) writes:
> > > > 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.Connectio n error '800a0e7a'
> > > > Provider cannot be found. It may not be properly installed.
> > > >
> > > > When i use following connection string
> > > > "Provider=SQLNC LI;Server=127.0 .0.1;
> > >
> > > Did you install SQL Native Client on the web server? SQL Native Client
> > > is a DLL that contains versions of both the ODBC SQL Server Driver
> > > and the SQL Server OLE DB Provider that has been enhanced with support
> > > for SQL 2005.
> > >
> > > SQL Native Client is freely distributable.
> > >
> > > > 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.
> > >
> > > Note that by default some editions of SQL 2005 by default only permits
> > > local connections. You can modify this in the Surface Area Configuration
> > > Tool.
> > >
> > > You may also have to encure that the SQL Browser service is running.
> > >
> > >
> > >
> > >
> > > --
> > > Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
> > >
> > > Books Online for SQL Server 2005 at
> > > http://www.microsoft.com/technet/pro...ads/books.mspx
> > > Books Online for SQL Server 2000 at
> > > http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color][/color][/color]

**Erland Sommarskog** · Jun 17 '06, 04:05 PM

Re: Connecting Sql Server 2005 from ASP?

MVChauhan@googl email.com (MVChauhan@goog lemail.com) writes:[color=blue]
> Thanks for your reply.
> I still cant conntect to SQL Server.
>
> 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
>
>[/color]
"Provider=SQLNC LI;Server=127.0 .0.1;Database=D BName;UID=UserN ame;PWD=UserPas s
word;"[color=blue]
>
> i get
>
> Microsoft SQL Native Client error '80040e4d'
> Login failed for user 'UserName'.[/color]

This is strange, because as I understood your original post, you were
connecting from a web server that was a on different machine from where
SQL Server is running.

However, in your connection string you have specified 127.0.0.1 which is
always the local machine. Still, you do make contact with an SQL Server
instance since you get that message.

Since I don't see your configuration, I'm left to guessworks, but there
are two obvious possibilities:
1) You have the username/password wrong.
2) You have an of SQL Server on the web server as well, but that's
not the one you intend to connect to.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**MVChauhan@googlemail.com** · Jun 19 '06, 08:25 AM

Re: Connecting Sql Server 2005 from ASP?

hi Erland

I am sorry if i confued you, you are right WebServer and DB Server are
2 different machines. I have replaced 127.0.0.1 with the IP of DB
server and now the error is

TCP Provider: An existing connection was forcibly closed by the remote
host.

Configuration of Servers are as follows

Webserver
OS Windows 2003

Database Server
OS Windows 2003
DB SQL Server 2005 with Service Pack1

Mits

Erland Sommarskog wrote:
[color=blue]
> MVChauhan@googl email.com (MVChauhan@goog lemail.com) writes:[color=green]
> > Thanks for your reply.
> > I still cant conntect to SQL Server.
> >
> > 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
> >
> >[/color]
> "Provider=SQLNC LI;Server=127.0 .0.1;Database=D BName;UID=UserN ame;PWD=UserPas s
> word;"[color=green]
> >
> > i get
> >
> > Microsoft SQL Native Client error '80040e4d'
> > Login failed for user 'UserName'.[/color]
>
> This is strange, because as I understood your original post, you were
> connecting from a web server that was a on different machine from where
> SQL Server is running.
>
> However, in your connection string you have specified 127.0.0.1 which is
> always the local machine. Still, you do make contact with an SQL Server
> instance since you get that message.
>
> Since I don't see your configuration, I'm left to guessworks, but there
> are two obvious possibilities:
> 1) You have the username/password wrong.
> 2) You have an of SQL Server on the web server as well, but that's
> not the one you intend to connect to.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]

**Erland Sommarskog** · Jun 19 '06, 01:25 PM

Re: Connecting Sql Server 2005 from ASP?

MVChauhan@googl email.com (MVChauhan@goog lemail.com) writes:[color=blue]
> I am sorry if i confued you, you are right WebServer and DB Server are
> 2 different machines. I have replaced 127.0.0.1 with the IP of DB
> server and now the error is
>
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
>
> Configuration of Servers are as follows
>
> Webserver
> OS Windows 2003
>
> Database Server
> OS Windows 2003
> DB SQL Server 2005 with Service Pack1[/color]

Have you configured SQL 2005 to accept remote connections? The Developer
and Express Editions of SQL 2005 accepts by default local connection only.

Use the Surface Area Configuration tool to check and change this.

Also make sure that the SQL Server Browser service is running.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**MVChauhan@googlemail.com** · Jun 19 '06, 04:25 PM

Re: Connecting Sql Server 2005 from ASP?

In SAC I have enabled Local and Remote Connection and also SQL Browser
service is running and is also Active.

Mits

Erland Sommarskog wrote:
[color=blue]
> MVChauhan@googl email.com (MVChauhan@goog lemail.com) writes:[color=green]
> > I am sorry if i confued you, you are right WebServer and DB Server are
> > 2 different machines. I have replaced 127.0.0.1 with the IP of DB
> > server and now the error is
> >
> > TCP Provider: An existing connection was forcibly closed by the remote
> > host.
> >
> > Configuration of Servers are as follows
> >
> > Webserver
> > OS Windows 2003
> >
> > Database Server
> > OS Windows 2003
> > DB SQL Server 2005 with Service Pack1[/color]
>
>
> Have you configured SQL 2005 to accept remote connections? The Developer
> and Express Editions of SQL 2005 accepts by default local connection only.
>
> Use the Surface Area Configuration tool to check and change this.
>
> Also make sure that the SQL Server Browser service is running.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]

**Erland Sommarskog** · Jun 19 '06, 09:45 PM

Re: Connecting Sql Server 2005 from ASP?

MVChauhan@googl email.com (MVChauhan@goog lemail.com) writes:[color=blue]
> In SAC I have enabled Local and Remote Connection and also SQL Browser
> service is running and is also Active.[/color]

And you did restart SQL Server after enabling remote connections?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**MVChauhan@googlemail.com** · Jun 20 '06, 11:55 AM

Re: Connecting Sql Server 2005 from ASP?

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

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=SQLNCL I.1;Persist Security Info=False;User
ID=UserNamer;Pa ssword=UserPass word;Initial Catalog=NameofD atabase;Data
Source=DBServer Name

Mits

Erland Sommarskog wrote:
[color=blue]
> MVChauhan@googl email.com (MVChauhan@goog lemail.com) writes:[color=green]
> > In SAC I have enabled Local and Remote Connection and also SQL Browser
> > service is running and is also Active.[/color]
>
> And you did restart SQL Server after enabling remote connections?
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]

Connecting Sql Server 2005 from ASP?

Connecting Sql Server 2005 from ASP?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment