Clever Geek Handbook

Classic ASP on Server 2008 and SQL 2008

I am currently migrating one of my clients' sites to a Windows 2008 and SQL 2008 server, but I am having serious problems connecting to the database from the site.

I have restored the database from SQL 2k backup to SQL 2008 server, I configured the user correctly and can login as that user in management studio perfectly. I copied the site's .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 passwords, new users are created, connection string changed from OLEDB to SQL Native Client and back again, but keep getting errors. I even set up a dummy database and user and still have the same problem.

Does anyone know why this might be happening? Is there a parameter in SQL or Windows that I am missing?

I was at this hour and would really appreciate any ideas.

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

+1


source to share


5 answers


I had several similar problems with our Windows 2008 and SQL Server 2008. From what I remember, these are the steps we went through to enable a connection using SQL Authentication

  • Logging on to SQL Server as an administrator, modifying server properties to allow mixed mode authentication (SQL Authentication and Windows Authentication)

  • Restart the SQL Server service

  • Using SQL Server Configuration Manager ensures that the enalbed protocols (TCP / IP)

We had quite a few interconnection issues with the firewall. Several times the login failed for the user was not the true cause of the problem. I usually create a udl file to test connectivity.



Are you using Windows Authentication when logging into your SQL Server?

Hope it helps.

+1


source


Sounds like a problem with ADO. Have you tried to install the latest version or update your installation if you already have the latest version?



Edit: Sorry didn't notice that you were running Windows Server 2008. This version ships with Windows DAC 6.0 and cannot be reinstalled as far as I know. Since your UDL file is working the only thing I can think of is to make sure the user id for your ASP site has access to the folders containing the dll dAC. You can try running FileMon from Sysinternals and check the access of forbidden entries to these files.

0


source


I got the solution, for the classic asp connection string:

myConnection.ConnectionString = "Driver={SQL Server};Server=xxx.xxx.xxx.xxx,1533;Database=mydb;Uid=user123;Pwd=user123d;"
0


source


I had a similar problem.

I changed the connection string from this:

DRIVER={SQL Server};SERVER=80.82.xxx.xxx;DATABASE=mydatabasename;UID=myusername;PWD=mypassword

For this:

Provider=SQLNCLI10.1;SERVER=80.82.xxx.xxx;DATABASE=mydatabasename;UID=myusername;PWD=mypassword

And it worked.

You might want to play around with different vendor settings.

0


source


Since you've restored your database to a new server, you probably now have an orphan user in your database. You need to connect the orphan user to the server login. See How to Associate an Existing SQL Server Login with an Existing SQL Server Database User of the same name for more information.

0


source






More articles:


All Articles