[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQLExpress Integrated Security not working

Posted on 2012-08-20
5
Medium Priority
?
1,189 Views
Last Modified: 2012-08-22
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.
0
Comment
Question by:dcass
  • 4
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38314611
What is your front end?  
Who is logging on and have they been added to SQL Server?
What is the error message?
0
 

Author Comment

by:dcass
ID: 38314674
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38314785
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38314793
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 38314796
Here is a pretty good discussion on IIS Authentication:
http://msdn.microsoft.com/en-us/library/ms955939.aspx
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Tech spooks aren't just for those who are tech savvy, it also happens to those of us running a business. Check out the top tech spooks for business owners.
Ransomware - Defeated! Client opened the wrong email and was attacked by Ransomware. I was able to use file recovery utilities to find shadow copies of the encrypted files and make a complete recovery.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question