0

I have a sql server 2008R2 express running in windows authentication mode. I created a login and a user for a database in sql server based on a windows account. I can connect to sql server database via(run as the user) SSMS and browse my database just fine.

Now i am trying to connect via a 3rd party asp application 

set loginDB = Server.CreateObject("ADODB.Connection")

'For Windows Server 2008 / SQL 2008 users, please use the following line for the DB connection

loginDB.Open "Provider=SQLNCLI10; Server=(local)\SQLServerName; Database=dbname;Uid="myuser" Pwd="pwd""

I am getting an error saying login failed for user myuser

Also the site is running on an app pool which i have set it to run as the windows user myuser.

Where am i going wrong???

Thanks.

Marc B
  • 356,200
  • 43
  • 426
  • 500
cableload
  • 4,215
  • 5
  • 36
  • 62
  • 1
    You're missing the semicolon between the uid and pwd parts. – JamieSee Apr 10 '12 at 16:43
  • Pretty sure IIS will pass along the Windows credentials of the user accessing the web site (once you change it to SSPI as Tony suggests). In most cases unless you are specifically trying to audit the individual Windows users' actions it is sufficient to use SQL authentication for generic database access from classic ASP. – Aaron Bertrand Apr 10 '12 at 17:14
  • Double Check that you have the correct dbname. SQL Server's error messages when using the incorrect DBName generally looks a lot like a invalid username/password error message. – My Other Me Apr 11 '12 at 07:01

1 Answers1

1

If you are passing username and password that's SQL Server Authentication not windows, so if it's authentication mode is Windows only, it won't have it.

Take them out and replace with Integrated Security = SSPI

Tony Hopkinson
  • 20,172
  • 3
  • 31
  • 39
  • You are right Tony.I replaced the username/password with the statement Integrated Security=SSPI. Now i am getting a different error Microsoft SQL Server Native Client 10.0 error '80004005' Cannot open database "dbname" requested by the login. The login failed. – cableload Apr 10 '12 at 18:21
  • and Do you have a database of that name on that instance, that the windows user has permission to access.... – Tony Hopkinson Apr 10 '12 at 22:45
  • Tony Thanks for your help. I think the issue was fixed by setting the credentials for anonymous authentication as explain in this link http://stackoverflow.com/questions/1139772/classic-asp-problem-connecting-to-remote-sql-server-database by Robin M – cableload Apr 11 '12 at 13:34