Please help me with asp sql server users/login

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

Please help me with asp sql server users/login

Author

Topic

Danny__T
Starting Member

27 Posts

Posted - 2004-04-01 : 19:48:02

I've been trying to suss this for hours now with no luck at all. I'm trying to connect to a sql server database on a win2003 webserver running IIS6 (both IIS and sql server on same machine).

I have set up the database and added a user 'CLUSR' with a password, I can log into query analyzer and return the info I want using this username and password.

I am consistantly getting the following error trying to connect with asp: -

Microsoft OLE DB Provider for SQL Server error '80004005'
Cannot open database requested in login 'my_database'. Login fails.
/test.asp, line 7

test is a simple asp file I created to try and get this to work and contains the following code: -

<%
Option Explicit

Dim myConn, rsTest, prodName

Set myConn = server.createobject("adodb.connection")
myConn.open "Provider=SQLOLEDB;Server=(local);User ID=CLUSR;Password=mypass;Database=my_database;"

strSQL = "SELECT products.* FROM products"
Set rsTest = server.createobject("adodb.recordset")
rsTest.open strSQL, myConn

rsTest.movefirst
Do while not rsTest.eof
prodName = rsTest.fields(1)
response.write(prodName & "<br>")
rsTest.movenext
Loop

set rsTest = nothing
rsTest.close

set myConn = nothing
myConn.close

%>

I've been going around in circles with sql server users, permissions, etc as well as getting completely lost in IIS security settings.

If anyone can shed any light on this at all you will gain instant promotion to the top of my christmas card list and save me from near imminent frustration overload!

Cheers

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-04-01 : 20:41:47

Make sure you are using tcp/ip for your connection protocol.
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer

See the section on running on a remote computer.

Make sure the server is running on port 1433.

Also try connecting via ip.

Check your mdac version.

also try it with

Provider=SQLOLEDB.1

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-01 : 22:57:09

Instead of Database=my_database; shouldn't it be Initial Catalog=my_database;?

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Danny__T
Starting Member

27 Posts

Posted - 2004-04-04 : 07:52:45

Well I managed to suss it, well I say suss I mean it now works but not exactly sure how. I know its somehting to do with user accounts but really cannot get my head around the whole Sql server users vs Windows Users vs IIS user access permissions

Can anyone explain (in simpletons language) how these all integrate?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-05 : 17:29:08

SQL Server has LOGINS that determine access to the server itself. These may be Windows users or they may be SQL Server specific logins with passwords. SQL Server databases have users which map to logins and control permissions within the database.

In IIS/ASP (NOT .Net) the default Windows User that is assigned to anyone browsing a web page anonymously (i.e. not logged in to the web site) is IUSR_machinename but it can be configured otherwise. If you want to use Windows Authentication only in your SQL Server, and don't want to force people to login to your site, then the anonymous web user account (IUSR_machinename) has to be added as a SQL Login.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources