Connecting Sql Server 2005 from ASP?

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"

Then 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.

I would really appreciate any help as this is driving me crazy.

Mits

ASP has not been supported for some time. However, if you're stuck with legacy ASP code, I would expect you'd be able to connect, as it just connects via COM, I thought.

However, web development questions are off topic here. www.asp.net is the place for ASP.NET questions, but as I said, there is no support for asp, and no Microsoft website that allows asp questions. I percieve that the question relates to connecting to SQL Server 2005, which is on topic here, so I'm not locking the thread or marking it as off topic ( which I'd usually do for ASP questions ), but certainly the odds of people here using ASP are very low, so I'd recommend finding a forum that actually supports ASP and asking there. If you're using ASP.NET, and that's what you meant to say, please ask at www.asp.net.

Good luck !!!

|||hi cgraus
Thanks for not locking the topic.

I have never come across anywhere, where its says i cant connect to SQL Server 2005 using ASP. If this is true then that means we can't move on to SQL Server 2005 as all our website still written in ASP and the database is SQL Server 2000.

Mits

|||

// If this is true then that means we can't move on to SQL Server 2005

Well, I guess the real question is, why do you want to ? I mean, your website is ( sorry ) written in a hopelessly out of date technology, so how will SS2005 help you anyhow ? It's not like you can write .NET code to run in it...

But, and this is my core point, I don't know if it's true or not. I've not worked on ASP sites for years, and the odds of me doing it again are zero. That's why you'd be better off finding a site that suports ASP, because it's very much legacy technology now, and unless you're talking to people who are using it, the odds of you getting an answer are pretty low.

|||I understand your point about ASP and getting the answer or solution in this forum.
I will really appreciate if you can give me any link to Microsoft's website where it says that any website written in ASP won't be supported by SS2005.

Mits

|||What i am asking here or atleast tyring to get the answer is;

As it is possible to connect to SQL 2000 using ASP code, Is it still possible to do with SQL Server 2005?

Mits

|||

I don't know, and that's the core point I'm trying to make. If I had a link, I'd have answered your question, but the fact is that it's unlikely that anyone here uses asp, and so it's unlikely that anyone here can help you.

It's unfortunate that it's very hard to google for asp and not asp.net info. -net actually doesn't work, I've discovered.

You didn't answer my question tho - why do you need SQL Server 2005 ? What does it offer to make it worth the cost and the hassle ? I assume you have a huge site, to not be updating it to ASP.NET, but without .NET, what does SQL Server 2005 offer that's so important ?

|||Its a management decision and i am asked to evaluate SS2005 in our business environment.
Based on my findings management will take decision when to migrate over SS2005. Our website are E-Commerce sites and because of lack of resources the tranisition process from ASP to ASP.Net is slow.

I know this is not the place but in line of this discussion, as a MVP what factors would you consider if you want to migrate to SS2005. (Sorry i was just asking cgraus but if anyone wants to throw any ideas then they are more then welcome to do so.)

Mits

|||moved this post to SQL Server getting started.|||

My core criteria with any upgrade is 'what does it give me'. For example, I have projects written in VC6. I don't like VC6, not one bit. But, I don't upgrade my projects to VC2005, or even VC2002, unless I can see a compelling reason to. ANY upgrade to a new product is in essence a move to a new programming language. I would be surprised in T-SQL 2005 changes anything that would stop T-SQL 2000 code from working, but it's still an identifiable risk that I would not take unless there were actual SS205 features that I needed. SQL Server is also not cheap. It's not money I would spend if my SS2000 database was performing just fine, and all I intended to do was run the exact same database on a new engine. The exciting new stuff in SS2005 relate to managed code, and unless you're moving to ASP.NET and intend to use those features ( for good reason, not just to say you did ), I wouldn't bother thinking about moving.

|||hi

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

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