June 13, 2006 at 7:14 am
I'm not sure if this is the right forum or not, but this is not an ASP.NET question per se, but a general connection question.
I don't know if this is a unique problem, or I'm going about it the wrong way. I currently connect to one of our SQL servers via a priviliged account (by using RUNAS). Works with no problem from Enterprise Manager or Query Analyzer. I now need the ability to connect to the same SQL server using ASP. I have the following connect string, but I'm not sure how to specify the domain in the string, or is there some other way?
<%
demoPath="Provider=SQLOLEDB;" & _
"Data Source=myserver;Integrated Security=SSPI;" & _
"Initial Catalog=qdb"
response.write demopath
Set demoConn = CreateObject("ADODB.Connection")
demoConn.open demoPath
response.end
%>
But here's the error I'm getting:
Provider=SQLOLEDB;Data Source=myserver1;Integrated Security=SSPI;Initial Catalog=qdb
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
June 13, 2006 at 7:26 am
Did you already try something like this?
demoPath="Provider=SQLOLEDB;" & _
"Data Source=domain\myserver;Integrated Security=SSPI;" & _
"Initial Catalog=qdb"
Or
demoPath="Provider=SQLOLEDB;" & _
"Data Source=""domain\myserver"";Integrated Security=SSPI;" & _
"Initial Catalog=qdb"
June 13, 2006 at 8:31 am
Just tried them both; same error. Other suggestions? 🙂
June 14, 2006 at 1:40 am
Since you are using integrated security, the user that executes the command should be known in the database and have all the rights needed. When you run something from ASP, the user trying to connect is IUSR_<machinename> unless you have IIS setup to use Integrated Security.
Easiest is to add the IUSR_<machinename> user with select rights to the SQL Server, and try again.
June 14, 2006 at 7:00 am
Is the server you are trying to connect to MS SQL Server 2000?
Try connecting using query analyzer. If you get the same error, then there is a problem with either the physical connection or authentication.
from the MS Windows cmd prompt do the following, >nslookup myserver
If you get the IP address, then your server is on the lan.
June 14, 2006 at 8:43 am
I think Erik is right, this is probably due to the fact that the IIS service is running under a user account which doesn't have privelages on the DB. Try either adding the user to the SQL server or changing the login for the IIS service to be a user that is recognised.
I normally change the default user account for the IIS service anyhow as it allows me to more closely monitor the privelages that account has in case of a security breach.
June 14, 2006 at 9:40 am
Okay, I'm a little confused guys (I know, not hard to do). As I stated I above I can log in successfully using Query Analyzer or Enterprise Manager using RUNAS with a privileged account. If I add a new user (IUSR_servername), I assume I use SQL Authentication?
June 20, 2006 at 8:59 am
Hello?
June 20, 2006 at 11:21 am
Hello.
Are you using ASP or ASP.NET?
Go into EM and make sure that your Windows ASP(.NET) user account has access to your SQL Server. When you access an ASP.NET app anonymously and it connects to SQL Server, it uses the IUSR_ Windows account credentials. You need to make sure that account has access. That or forcing authentication for your ASP.NET app should do the trick.
June 20, 2006 at 12:07 pm
It would be a trusted connection.
If you have added the IUSR_<machine> account to the SQL Server, and that account has select permisison to the table you'll be good to go.
in ASP a trusted connection - connection string looks like:
DRIVER=SQL Server;SERVER=<servername>;DATABASE=<DB name>;TRUSTED_CONNECTION=yes;
June 21, 2006 at 8:42 am
MikeC wrote:
Are you using ASP or ASP.NET?
ASP
Go into EM and make sure that your Windows ASP(.NET) user account has access to your SQL Server. When you access an ASP.NET app anonymously and it connects to SQL Server, it uses the IUSR_ Windows account credentials.
So the connect string I'm using is connecting anonymously? Here's my connect string:
demoPath="Provider=SQLOLEDB;" & _
"Data Source=myserver;Integrated Security=SSPI;" & _
"Initial Catalog=qdb"
June 21, 2006 at 8:57 am
Presumably your browser is connecting anonymously to your web server. When you connect to the web server anonymously, everything it does and all of its interactions with the server and/or network resources is done under the credentials of the IUSR_ Windows account. If the IUSR_ Windows account does not have access to SQL Server, then IIS will not have access to SQL Server when you use your browser to pull up your ASP App which is running on IIS.
June 26, 2006 at 6:54 am
Mike,
Does my connect string indicate I'm connecting anonymously? I'm a newbie with this ASP stuff. Note I can connect using Query Analyzer and Enterprise Manager just fine. I'm confused about the connect string though.
June 26, 2006 at 7:29 am
You can't connect anonymously to SQL Server. You're connect string indicates you're using Windows Integrated Authentication to connect to SQL Server. All this means is when you connect to SQL Server via ASP.NET, ASP.NET needs to use a Windows Account to log in to SQL Server. That Windows Account needs access to your SQL Server.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply