-1

I'm trying to setup a test environment on my own machine, of our company website written in classic asp. The idea is to use integrated windows authentication to connect to the database.

I'm useing the following connection string

Provider=SQLOLEDB.1;Server=localhost;Database=Filbert;Integrated Security=SSPI;

But the browser responds with

Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

In the SQL log I can see

Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 16.

A state 16 errors is whe

I'm running Windows XP SP3, IIS 5.1, SQL server 2005

EDIT: I've also created and tested an login using SQL authentication instead. I'm able to login with the SQL authentication using the SQL management studio just fine, but get the same errors when trying from ASP

David Lemvigh
  • 11
  • 2
  • Did you check that SYSTEM actually has access to that database? It's not that SYSTEM automatically can do everything. – Tomalak Jun 18 '12 at 09:38
  • Side node: You should definitely not run your web site in the local system context. Create a dedicated, low privilege account for IIS and grant that account access to the database. – Tomalak Jun 18 '12 at 09:48
  • The SQL server has a login for the NT AUTHORITY\SYSTEM account, with access to the database. – David Lemvigh Jun 18 '12 at 10:10
  • The low-priveledge account seems to be a feature of iis 6, which unfortunately for me doesn't exist for winxp. Otherwise I would have followed this [guide](http://learn.iis.net/page.aspx/624/application-pool-identities/) – David Lemvigh Jun 18 '12 at 10:15
  • 1
    You can run IIS5 under a different service account (Services control panel). I'm sorry but *"Login failed for user 'NT AUTHORITY\SYSTEM'"* is pretty unambiguous. It is a plain and simple access denied message, you must double-check your SQL server login configuration. Also see [this answer](http://stackoverflow.com/a/6894702/18771) which comes to the same conclusion. – Tomalak Jun 18 '12 at 10:21
  • The 'NT AUTHORITY\SYSTEM' account is also something that confuses me. Even though I've changed my connection string to use SQL authentication, the login still fails and the user is still NT AUTHORITY\SYSTEM in the sql log. – David Lemvigh Jun 18 '12 at 10:52
  • My course of action would be to first switch IIS to a dedicated, non-privileged account (create a standard user). Then allow that account in SQL Server. Then try again with SQL Native Client and integrated security. `Provider=SQLNCLI10;Server=localhost;Database=Filbert;Trusted_Connection=yes;` – Tomalak Jun 18 '12 at 11:10

2 Answers2

0

I suggest you create a specific user in SQL server, test the connection to the db with that user and then in your asp code do the following

<%
Provider=SQLOLEDB.1;Password=YourPassword;Persist Security Info=True;User ID=YourUserID;_
       Initial Catalog=YourDB;Data Source=YOUR_LOCAL_SERVER_NAME

 Set commInsert = Server.CreateObject("ADODB.Connection")
 commInsert.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;_
     PersistSecurity Info=False;Initial Catalog=_
     YourDB;_Data Source=YOUR_LOCAL_SERVER_NAME

 'now you can eg do an insert
 strInsertCommand = "INSERT INTO tableName(TextField, NumericField)_
     VALUES('ABCDE', 12345);"
 commInsert.Execute(strInsertCommand) ' Execute the insert command
 commInsert.Close()
 Set commInsert = Nothing
 %>
peter
  • 41,770
  • 5
  • 64
  • 108
0

I've managed to fix the problem by switching the SQL provider to SQLNCLI

Provider=SQL;Server=localhost;Database=Filbert;Integrated Security=SSPI;
David Lemvigh
  • 11
  • 2