SQLExpress Integrated Security not working

I would like to get integrated security working on my installation of SQLExpress 10.50
instead of using the user name and password in the code.

Here is the current code (which works):
  Set SQL_Cn = server.createobject("ADODB.Connection")
  SQL_Cn.Provider = "sqloledb"
  SQL_Cn.Properties("Data Source").Value = "CWEB\SQLEXPRESS"
  SQL_Cn.Properties("Initial Catalog").Value = "RT"
  SQL_Cn.Properties("User ID").Value = "xxx"
  SQL_Cn.Properties("Password").Value = "xxx"
  SQL_Cn.open
  Set SQL_Rs = Server.CreateObject("ADODB.Recordset")
  Set SQL_Rs2 = Server.CreateObject("ADODB.Recordset")
  SQL_RS.ActiveConnection = SQL_Cn

Here is what I'd like to do but it won't connect:
  Set SQL_Cn = server.createobject("ADODB.Connection")
  SQL_Cn.Provider = "sqloledb"
  SQL_Cn.Properties("Data Source").Value = "CWEB\SQLEXPRESS"
  SQL_Cn.Properties("Integrated Security").Value="SSPI"
  SQL_Cn.Properties("Initial Catalog").Value = "RT"
  SQL_Cn.open
  Set SQL_Rs = Server.CreateObject("ADODB.Recordset")
  Set SQL_Rs2 = Server.CreateObject("ADODB.Recordset")
  SQL_RS.ActiveConnection = SQL_Cn

The SQL server is connected to the SQL Server Management Studio using Windows Authentication.

Security/Users for database are:
dbo
guest
INFORMATION_SCHEMA
sys
xxx  with datareader and datawriter

Security/Logins for all databases:
BUILTIN/Users
Administrator
NT Authority/System
NT Service\MSSQL$SQLEXPRESS
NT Authority/Local Service
xxx
and it's got these two entries too:
##MS_PolicyEventProcessingLogin##
##MS_PolicyTsqlExecutionLogin##



What do I need to do to get this working?  It should be so simple, but I've been working on this for 2 days.
dcassAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Here is a pretty good discussion on IIS Authentication:
http://msdn.microsoft.com/en-us/library/ms955939.aspx
0
 
Anthony PerkinsCommented:
What is your front end?  
Who is logging on and have they been added to SQL Server?
What is the error message?
0
 
dcassAuthor Commented:
This is the include on every ASP (classic) pgm in the system.
There is no log in.
The error message is that it can't make the connection (when using Integrated Security).
0
 
Anthony PerkinsCommented:
In ASP the user is connected anonymously and does not have a Windows login. (Actually that is not entirely true, but it is not a login that you want to add to SQL Server).  Your best bet is to continue to use SQL Server connections.
0
 
Anthony PerkinsCommented:
If you still insist on using Windows Authentication, than you will have to add the IUSR_machinename to SQL Server as a login and the appropriate permissions for the user.  I don't recommend this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.