Connection String for classic ASP global.asa to SQL Server 2005

I migrated my SQL Server 7 database to the new SQL Server 2005 that I installed on my PC. I have classic ASP programs on my PC that used to access the SQL Server 7 database. However, global.asa and these ASP programs can no longer connect to the new SS 2005 database.

The access to the SS 2005 instance is with Local System for "Log on as".

I've tried all the following connection strings in my global.asa and they don't work.

dbConnect = "Data Source=CURT-DELL/curt;Initial Catalog=MyDatabaseName;Persist Security Info=True;User ID=curt;Password=_________"

dbConnect = "Data Source=C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabaseName_DATA.MDF;Initial Catalog=MyDatabaseName;Integrated Security=True"

dbConnect = "Data Source=MyComputerName/curt;Initial Catalog=MyDatabaseName;Trusted_Connection=Yes"

dbConnect = "Provider = SQLOLEDB.1;Data Source = (local)\MSSQLSERVER;Initial Catalog=<MyDatabaseName>;User ID = <curt>;Password = <________>;"

bConnect = "Data Source=MyComputerName;Initial Catalog=MyDatabaseName;Integrated Security=True"

dbConnect = "Driver={SQL Server};" & _

"Server=(local);" & _

"Database=MyDatabaseName;Uid=MyComputerName\curt;"

dbConnect = "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = MyDatabaseName; User Id = MyComputerName\curt; Password=__________"

Does anybody know how to solve the problem

Answer this question

Connection String for classic ASP global.asa to SQL Server 2005

DarrenMcD

Passwords are always case sensitive but if the server is configured as case-sensitive then all object names (databases, tables, stored procedures, columns... everything) and TSQL is case-sensitive. I never recommend installing SQL Server as case-sensitive any more as you can get the case-sensitive benefits by judicious use of TSQL operators that test for case on the fly.

hth
gazalec
Nope, SQL Server 2005 is not case-sensitive by default. While it can be installed that way, the TSQL and the ConnectionString are not case-sensitive unless you configure it so. Do you have a reproducible example
ergb10

SQL Server 2005 is very different than SQL Server 7. It has several more security walls that prevent access. I suggest looking at the whitepaper on my blog that discusses how to get connected. I would also consider moving to ASP.NET as it's far more stable, performs better and supports the .NET Framwork which integrates better with the current set of tools. See http://betav.com/blog/billva/2006/06/getting_and_staying_connected.html

Consider that while both SQL Server and SSPI authentication make sense, they both require special configuration on the target SQL Server. SQL Server only supports SSPI authentication by default. This also means that you need to configure the SQL Server account that's used by IIS to handle ASP connections. This account needs to be granted access to the target database (Initial Catalog).

hth
DenemeTR
Thanks for the suggestions. All that was very informative reading. I have bookmarked it all. However, I have gotten my connections to work. It seems that 2005 is now case sensitive. I have not read anywhere about this as a difference, but all I can say is that 'CASE' makes a difference.
Ryan.Kelley
I just started trying to connect my classic applications to sql server 2005 today and am experiencing the same problems you have reported. Just wondering if you had any success since you posted
Lengo
Thanks for your posting. I tried both of your connection strings, but to no avail. I've already tried the connection strings at www.connectionstrings.com. I've even tried contacting the author at

This is incredibly frustrating.

I've even tried "(local)" instead of MyComputerName.

I tried to confirm the User Id and Password in SQL Server 2005. When I look at the properties for the Users in SQL Server 2005, it doesn't show the passwords for the users. Do you know why it doesn't
Jason W158421

Then, they must have set us up to be case sensitive. It made a difference for all of my apps. Classic and asp.net.

What about the passwords Once I got connected, I had to modify the passwords in my connection strings to the proper case. The logins were a mix of upper and lower case but the old strings were just lower case. They work fine with the 2000 databases, but I had to use the proper case for the 2005 versions.
Ken Culp
what logon authentication are you using Windows or SQL Server

for Windows:

dbConnect = "Provider=SQLOLEDB; Data Source = Data Source=MyComputerName\MSSQL; Initial Catalog = MyDatabaseName; Integrated Security=SSPI"

for SQL Server:
dbConnect = "Provider=SQLOLEDB; Data Source = Data Source=MyComputerName\MSSQL; Initial Catalog = MyDatabaseName;User Id=curt; Password=__________"

keep this URL handy: http://www.connectionstrings.com
Connection String for classic ASP global.asa to SQL Server 2005

Answer this question