Connections are flaky

Got a sql server 2000 unlimited user license running on a Dell Server (1
CPU). There are several automated programs running 24 -7 that need to access
the database, each running on its own PC. Problem is the programs will run
flawlessly for days and hours, connecting, doing their stuff, closing the
connections (they are in VB6 or VB.NET). They all use the same connection
strings and work with ADO OLEDB connections.
Then all of a sudden on on or the other machine a program that needs to
connect to the server will refuse to connect, with a valid connection string
(we trap it, its OK). The typical error we get back will be server not
found, but if we ping the server either by its name or by IP address on the
computer that is giving us the error, we get a ping response back OK.
We're looking at a possible hardware failure but then we can use the SQL
database OK from within Enterprise Mgr, no problem.
It does not make any sense. Has anyone come accross a similar situation,
what can we do to make this more reliable?
Any help greatly appreciated, we're at our wits end.
BobDoes it ever resolve itself, or once the connection is broken, it cannot
reconnect without restarting the program?!?
Could it be a DNS issue? Could it be that some of the VB6 applications are
not returning all of their resources properly?
I just throwing some ideas out there.
Rick Sawtell
MCT, MCSD, MCDBA
"RD" <nospam@.nospam.net> wrote in message
news:%23oouP$IsEHA.1816@.TK2MSFTNGP15.phx.gbl...
> Got a sql server 2000 unlimited user license running on a Dell Server (1
> CPU). There are several automated programs running 24 -7 that need to
access
> the database, each running on its own PC. Problem is the programs will run
> flawlessly for days and hours, connecting, doing their stuff, closing the
> connections (they are in VB6 or VB.NET). They all use the same connection
> strings and work with ADO OLEDB connections.
> Then all of a sudden on on or the other machine a program that needs to
> connect to the server will refuse to connect, with a valid connection
string
> (we trap it, its OK). The typical error we get back will be server not
> found, but if we ping the server either by its name or by IP address on
the
> computer that is giving us the error, we get a ping response back OK.
> We're looking at a possible hardware failure but then we can use the SQL
> database OK from within Enterprise Mgr, no problem.
> It does not make any sense. Has anyone come accross a similar situation,
> what can we do to make this more reliable?
> Any help greatly appreciated, we're at our wits end.
> Bob
>

Connections are flaky

Got a sql server 2000 unlimited user license running on a Dell Server (1
CPU). There are several automated programs running 24 -7 that need to access
the database, each running on its own PC. Problem is the programs will run
flawlessly for days and hours, connecting, doing their stuff, closing the
connections (they are in VB6 or VB.NET). They all use the same connection
strings and work with ADO OLEDB connections.
Then all of a sudden on on or the other machine a program that needs to
connect to the server will refuse to connect, with a valid connection string
(we trap it, its OK). The typical error we get back will be server not
found, but if we ping the server either by its name or by IP address on the
computer that is giving us the error, we get a ping response back OK.
We're looking at a possible hardware failure but then we can use the SQL
database OK from within Enterprise Mgr, no problem.
It does not make any sense. Has anyone come accross a similar situation,
what can we do to make this more reliable?
Any help greatly appreciated, we're at our wits end.
BobDoes it ever resolve itself, or once the connection is broken, it cannot
reconnect without restarting the program?!?
Could it be a DNS issue? Could it be that some of the VB6 applications are
not returning all of their resources properly?
I just throwing some ideas out there.
Rick Sawtell
MCT, MCSD, MCDBA
"RD" <nospam@.nospam.net> wrote in message
news:%23oouP$IsEHA.1816@.TK2MSFTNGP15.phx.gbl...
> Got a sql server 2000 unlimited user license running on a Dell Server (1
> CPU). There are several automated programs running 24 -7 that need to
access
> the database, each running on its own PC. Problem is the programs will run
> flawlessly for days and hours, connecting, doing their stuff, closing the
> connections (they are in VB6 or VB.NET). They all use the same connection
> strings and work with ADO OLEDB connections.
> Then all of a sudden on on or the other machine a program that needs to
> connect to the server will refuse to connect, with a valid connection
string
> (we trap it, its OK). The typical error we get back will be server not
> found, but if we ping the server either by its name or by IP address on
the
> computer that is giving us the error, we get a ping response back OK.
> We're looking at a possible hardware failure but then we can use the SQL
> database OK from within Enterprise Mgr, no problem.
> It does not make any sense. Has anyone come accross a similar situation,
> what can we do to make this more reliable?
> Any help greatly appreciated, we're at our wits end.
> Bob
>

Connections are flaky

Got a sql server 2000 unlimited user license running on a Dell Server (1
CPU). There are several automated programs running 24 -7 that need to access
the database, each running on its own PC. Problem is the programs will run
flawlessly for days and hours, connecting, doing their stuff, closing the
connections (they are in VB6 or VB.NET). They all use the same connection
strings and work with ADO OLEDB connections.
Then all of a sudden on on or the other machine a program that needs to
connect to the server will refuse to connect, with a valid connection string
(we trap it, its OK). The typical error we get back will be server not
found, but if we ping the server either by its name or by IP address on the
computer that is giving us the error, we get a ping response back OK.
We're looking at a possible hardware failure but then we can use the SQL
database OK from within Enterprise Mgr, no problem.
It does not make any sense. Has anyone come accross a similar situation,
what can we do to make this more reliable?
Any help greatly appreciated, we're at our wits end.
Bob
Does it ever resolve itself, or once the connection is broken, it cannot
reconnect without restarting the program?!?
Could it be a DNS issue? Could it be that some of the VB6 applications are
not returning all of their resources properly?
I just throwing some ideas out there.
Rick Sawtell
MCT, MCSD, MCDBA
"RD" <nospam@.nospam.net> wrote in message
news:%23oouP$IsEHA.1816@.TK2MSFTNGP15.phx.gbl...
> Got a sql server 2000 unlimited user license running on a Dell Server (1
> CPU). There are several automated programs running 24 -7 that need to
access
> the database, each running on its own PC. Problem is the programs will run
> flawlessly for days and hours, connecting, doing their stuff, closing the
> connections (they are in VB6 or VB.NET). They all use the same connection
> strings and work with ADO OLEDB connections.
> Then all of a sudden on on or the other machine a program that needs to
> connect to the server will refuse to connect, with a valid connection
string
> (we trap it, its OK). The typical error we get back will be server not
> found, but if we ping the server either by its name or by IP address on
the
> computer that is giving us the error, we get a ping response back OK.
> We're looking at a possible hardware failure but then we can use the SQL
> database OK from within Enterprise Mgr, no problem.
> It does not make any sense. Has anyone come accross a similar situation,
> what can we do to make this more reliable?
> Any help greatly appreciated, we're at our wits end.
> Bob
>

Connection to SQL server Help SOS

I have SQL Server2000 installed on my PC when I am trying to do some programs in ASP.NET application. I have serious problem working with the SQL server 2000.Whenever I use visual tools to connect to the server or manually. Explorer returns error
//////////////////////////////////////////////////////////////////////////////////////////////
Server Error in '/ASP.NetPage530' Application.

Login failed for user 'ZEUS\ASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'ZEUS\ASPNET'.

Source Error:

Line 88: Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Line 89: If Not IsPostBack Then
Line 90: SqlDataAdapter1.Fill(DsCustID1, "Customers")
Line 91: DataBind()
Line 92: End If

Source File: C:\Inetpub\wwwroot\ASP.NetPage530\WebForm1.aspx.vb Line: 90

Stack Trace:

[SqlException: Login failed for user 'ZEUS\ASPNET'.]
System.Data.SqlClient.SqlConnection.Open()
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
System.Data.Common.DbDataAdapter.Fill(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
ASP.NetPage530.WebForm1.Page_Load(Object sender, EventArgs e) in C:\Inetpub\wwwroot\ASP.NetPage530\WebForm1.aspx.vb:90
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()
////////////////////////////////////////////////////////////////////////////////////////////////////////
No errors exist when I work with windows application both in ACCESS or SQL SERVER Databases and no errors exists when I am using access files in asp.net applicationand in both cases i use Windows NT Integrated Security .Most probably I need to configure my IIS5.0 but how I have no idea
Can anyone help?You may need to grant 'ZEUS\ASPNET' login permissions to your SQL Server. You can do this directly in SQL Server Enterprise Manager if you have it. Otherwise, you will need to use SQL language by running osql.exe from the command line.|||Yes but in command promt it asks for a password I have only one one pasword is system password which is invalid in command promt for osql.exe how i can configure it ?please help|||Type osql /? at the command line.

This gives you a list of parameters you can use to run the program.

I think you can use the-E parameter for a trusted connection.|||Thank you trying help me but I have no idea how to use –E parameters
I have tried the following command in command prompt osql/E the output was the number 1 what it means I don't know pushing the enter gives 2 and etc
what i have to do to enable trusted connections ???|||
If you are seeing this:

1
That means you are logged in succesfully.

Now you can execute any SQL statement. Each statement can consist of multiple lines. When you are finished, typeGO on a new line and hit enter. For Example (type enter at the end of each line):

1>Select name
2>From sysxlogins
3>go

This will display a list of current logins for your database.

What you need to do next is find some documentation on SQL language on how to add logins and grant them access to databases and database objects.

Have a look here for granting an NT user login permissions:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ga-gz_8dri.asp

If you like, you can go here for security in general:http://msdn.microsoft.com/library/en-us/adminsql/ad_security_05bt.asp?frame=true

I hope this helps.

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?

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

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?

|||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?

|||

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

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

|||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?|||

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.

|||

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

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?

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

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?

|||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?

|||

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

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

|||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?

|||

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.

|||

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

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?

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

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?

|||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?

|||

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

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

|||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?|||

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.

|||

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

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?

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

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?

|||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?

|||

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

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

|||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?|||

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.

|||

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

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?

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

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?

|||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?

|||

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

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

|||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?

|||

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.

|||

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