Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > BOOK: Beginning ASP 3.0
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning ASP 3.0 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 11th, 2003, 05:46 AM
Registered User
 
Join Date: Jun 2003
Location: asheville, nc, .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default connection string to MSDE(SQL) database from ASP

I am trying to use my home computer as a development machine. It has Windows XP Prof as the operating system and I have installed Office Professional for Access, Excel, etc. I have an application using Access Data Project (.adp) as the front end and MSDE (SQLServer) as the backend. The connection is OLEDB and it works fine. I am trying to convert the front end to ASP. My problem is I can't get the connection string to work. I get the following error when I try to simulate Chapter 12 page 476:

Technical Information (for support personnel)

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'HP864N\IUSR_HP864N'.
/BegASPFiles/Connect.asp, line 24


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.0.3705)

Page:
GET /BegASPFiles/Connect.asp

This particular message I got after I upsized the Movie2000.mdb to create a Movie2000CS.adp front end with a Movie2000SQL.mdf back end using MSDE. I selected WinowsNT security as opposed to Username sa and password null.

I have tried several other variations including installing Northwind with same results. Some variations have indicated "not a trusted userid".

I am sure this is simple but I can't get off this dime. Can you help?
  #2 (permalink)  
Old June 11th, 2003, 06:42 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

It would help if we could see your connection string, but I guess you've tried quite a few variations. For SQL Server connections using a windows authentication, my connection string usually looks like this:
Code:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbname;Data Source=servername
Another problem may be that the windows user IUSR_HP864N may not be a valid database user. Have you set-up permissions for this user in the db? If not try running something like this script to grant log-in to your db:
Code:
-- Create Server logins
USE Master
go
EXEC sp_grantlogin 'domain\user'
go

-- Grant access to database
USE dbname
GO
EXEC sp_grantdbaccess 'domain\user', 'user'
GO
  #3 (permalink)  
Old June 11th, 2003, 06:47 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

this site http://www.able-consulting.com/ADO_Conn.htm is v useful for connection strings.

A good way to get a connection string is to create a blank .udl file, double-click it, fill in the details in the dialog that appears, then open the udl file in Notepad when you've finished and you've got your connection string.

hth
Phil
  #4 (permalink)  
Old June 11th, 2003, 08:02 AM
Registered User
 
Join Date: Jun 2003
Location: asheville, nc, .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Additional info gleaned from suupport.microsoft.com in Knowledge Base Article 319930:
MSDE uses two authentication modes:
Windows Authentication Mode (Windows Authentication)
In Windows Authentication Mode, a user can connect through a Microsoft Windows NT 4.0, a Microsoft Windows 2000, or a Microsoft Windows XP user account.
Mixed Mode (Windows Authentication and SQL Server Authentication)
In Mixed Mode, users can use either Windows Authentication or SQL Server Authentication to connect to an instance of MSDE. Users who connect through a Windows NT 4.0, a Windows 2000, or a Windows XP user account can use trusted connections in either Windows Authentication Mode or Mixed Mode.
The default authentication mode for MSDE is Windows Authentication. MSDE installs with a built-in system administrator (SA) user account. However, because SQL Server Authentication is disabled by default, you cannot access the built-in account after a typical installation.

This article seems to imply that Microsoft recommends enabling Mixed Mode authentication but after installation this requires a registry key hack. Further since I don't have Enterprise Manager I must use OSQL, a command line utility installed with MSDE. Does it have to be this tuff???
  #5 (permalink)  
Old June 11th, 2003, 01:08 PM
Imar's Avatar
Wrox Author
Points: 72,022, Level: 100
Points: 72,022, Level: 100 Points: 72,022, Level: 100 Points: 72,022, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,076
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

No, I don't think it has to be so tough (although I am not 100% sure).

If you download the 120 day (or so) trial of SQL Server 2000 from the Microsoft site you'll get the Client tools (including the Enterprise Manager and Query Analyzer) with the download / CD. AFAIK (but like I said, I am not 100% sure) the 120 limit / license doesn't count for the client tools, so you can use them for your MSDE installation even after your trial expires.

Once you have those tools, it should be easy to switch security models. IMO, you're better of (security wise) using Integrated Security, although sometimes SQL Server authentication has its advantages (single user that connects to the database so it's easier to use Integrated Security on a website for example).

Cheers,

Imar
  #6 (permalink)  
Old June 11th, 2003, 10:51 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 111
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Microsoft recommends using Windows Only Authentication *not* Mixed Mode. Mixed Mode is still available for backwards compatibility reasons (eg your application uses SQL Server authentication, not Windows Authentication).

You can find the necessary reg key here:
http://support.microsoft.com/default...b;en-us;322336
(section 3: how to determine and change your authentication mode)

Cheers
Ken

www.adOpenStatic.com
  #7 (permalink)  
Old June 12th, 2003, 09:25 AM
Registered User
 
Join Date: Jun 2003
Location: asheville, nc, .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I tried the string Connection recommended above for OLE DB for SQL Server (To connect to SQL Server running on the same computer) and got an error "not a trusted SQL Server connection". The Usre ID was "sa" and password was "".

Assuming I want to stay with Windows Authentication instead of Mixed Mode, if I change sa password a non-blank password should a string connection like one the following work or is there more to do?

 ODBC Driver for SQL Server
For Standard Security

oConn.Open "Driver={SQL Server};" & _
           "Server=MyServerName;" & _
           "Database=myDatabaseName;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword"

For Trusted Connection security

oConn.Open "Driver={SQL Server};" & _
           "Server=MyServerName;" & _
           "Database=myDatabaseName;" & _
           "Uid=;" & _
           "Pwd="
' Or
oConn.Open "Driver={SQL Server};" & _
           "Server=MyServerName;" & _
           "Database=myDatabaseName;" & _
           "Trusted_Connection=yes"

To connect to SQL Server running on the same computer

oConn.Open "Driver={SQL Server};" & _
           "Server=(local);" & _
           "Database=myDatabaseName;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword"


  #8 (permalink)  
Old June 12th, 2003, 08:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 111
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

If you use Windows Authentication you *cannot* connect as "sa". "sa" is an SQL Server login. Windows Authentication means that you login to the SQL Server using your Windows Account, not with an account stored in SQL Server. For your ASP pages to login to SQL Server, you need to work out what user context the ASP pages are running under, and give that Windows account permission to login to the database. Usually ASP pages run under the default anonymous internet user account (IUSR_<machinename>), so you'd need to give that account permission to login to the database.

For Windows Authentication (or "trusted authentication" as it is otherwise known), you *do not* specify a uid or pwd in the connection string, because the Windows account that the process is running under is used to login to the SQL Server. You just do:

strConnect = _
"Provider=SQLOLEDB;" & _
"Data Source=myComputerName;" & _
"Initial Catalog=myDatabaseName;" & _
"Integrated Security=SSPI"

Cheers
Ken

www.adOpenStatic.com
  #9 (permalink)  
Old June 13th, 2003, 06:03 PM
Registered User
 
Join Date: Jun 2003
Location: asheville, nc, .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Technical Information (for support personnel)

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'HP864N\IUSR_HP864N'.
/BegASPFiles/Connect.asp, line 23


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.0.3705; MSN 8.0; MSN 8.5; MSNbBBYZ; MSNmen-us; MSNcIA)

Page:
GET /BegASPFiles/Connect.asp

Time:
Friday, June 13, 2003, 6:59:08 PM


More information:
Microsoft Support

Here is code:
<%
  Option Explicit
  Dim strConnect
%>

<HTML>
<HEAD>
<TITLE>Using an SSI to hold the Connection String</TITLE>
</HEAD>
<BODY>

<%
  Dim objConn, objRS
  Dim adOpenForwardOnly, adLockReadOnly, adCmdTable

  adOpenForwardOnly = 0
  adLockReadOnly = 1
  adCmdTable = 2

  Set objConn = Server.CreateObject("ADODB.Connection")
  Set objRS = Server.CreateObject("ADODB.Recordset")
  strConnect = "Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=HP864n;Initial Catalog=BCDataSQL"
  objConn.Open strConnect

  objRS.Open "Workcentermaster", objConn, adOpenForwardOnly, adLockReadOnly, adCmdTable

  While Not objRS.EOF
    Response.Write objRS("wcwc") & "<BR>"
    objRS.MoveNext
  Wend

  objRS.Close
  objConn.Close
  Set objRS = Nothing
  Set objConn = Nothing
%>

</BODY>
</HTML>

Test Connection OK fro Access adp front end. What else?????
  #10 (permalink)  
Old June 13th, 2003, 06:09 PM
Imar's Avatar
Wrox Author
Points: 72,022, Level: 100
Points: 72,022, Level: 100 Points: 72,022, Level: 100 Points: 72,022, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,076
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi there,

The error indicates that the user 'HP864N\IUSR_HP864N' does not have access to the database.

To solve this, open the SQL Enterprise Manager and add this user under Security | Logins. Make sure the account has access to just the databases it is allowed to see.

Then, in the database, fine-tune the permissions for this user. It's tempting to make it DBO, but don't do that. Take some time to consider the permissions this account needs.

I usually have all my data access through Stored Procedures, so I just give execute permissions on the sprocs for this account and nothing else.

HtH,

Imar
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Connection string for MSDE srkarthik_82 General .NET 1 January 25th, 2007 01:35 AM
sql server connection problem in msde oldbig89 SQL Server 2000 1 February 8th, 2006 05:46 PM
Porting MSDE database to SQL server hgberman SQL Server ASP 2 January 23rd, 2005 08:03 PM
ASP.NET MSDE Connection string problems Bagheer Classic ASP Databases 0 August 27th, 2003 08:02 PM



All times are GMT -4. The time now is 12:49 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.