Classic ASP On Server 2008 & SQL 2008

Question

I am currently migrating one of my clients sites to a windows server 2008 and SQL 2008 setup, but I am having massive problems with connecting to the database from the site.

I have restored the database from a SQL 2k backup into the SQL 2008 server, I have setup the user correctly and can login as that user in management studio fine. I have copied over the site .asp files which load fine when there is no database access. But when i try to access the database it fails with "Login Failed for user......".

I have reset the passwords, created new users, changed the connectionstring from OLEDB to SQL Native Client and back again but keep getting errors. I have even setup a dummy database and user and still have the same problem.

Does anyone know of a reason why this could be happening? Is there a setting in SQL or windows that I am missing?

I have been at this for hours and would really appreciate any ideas.

UPDATE : If I put the wrong login details in the connection string I get the error on conn.open but if I put in the correct login details I get the error on cmd.activeconnection = conn. Not sure if that helps.

Answer 1

Hi Chris, I have just had a few similar problems with our Windows 2008 and SQL Server 2008. From what i can remember these are the steps we went through to enable connectivity using SQL Authentication

1. Logon to the SQL Server as an Administrator, change the properties of the server to allow mixed mode authentication (SQL Authentication and Windows Authentication)

2. Restart the SQL Server service

3. Using the SQL Server Configuration manager ensure that the protocols are enalbed (TCP/IP)

We had quite a few firewall issues, regarding conectivity. Some times the Logon Failed for user wasn't the true cause of the problem. I usually create a file with a udl extension to test for connectivity.

When you logon to your SQL Server do you use windows authentication?

Hope this helps.

Answer 2

Sounds like a problem with ADO. Have you tried installing the latest version or refreshing the install if you already have the latest one?

Edit: Sorry, didn't notice you were running Windows Server 2008. That version comes with Windows DAC 6.0, and that can't be reinstalled as far as I know. Since your UDL file works, the only thing I can think of is to make sure the user identity for your ASP site has access to the folders containing the DAC dlls. You can try running FileMon from Sysinternals and checking for access denied entries to those files.

Answer 3

i got the solution................ for classical asp connection string is myConnection.ConnectionString = "Driver={SQL Server};Server=xxx.xxx.xxx.xxx,1533;Database=mydb;Uid=user123;Pwd=user123d;"