p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: Beginning ASP 3.0 (http://p2p.wrox.com/forumdisplay.php?f=27)
-   -   connection string to MSDE(SQL) database from ASP (http://p2p.wrox.com/showthread.php?t=423)

johngilbart June 11th, 2003 05:46 AM

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?

pgtips June 11th, 2003 06:42 AM

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


pgtips June 11th, 2003 06:47 AM

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

johngilbart June 11th, 2003 08:02 AM

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???

Imar June 11th, 2003 01:08 PM

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

KenSchaefer June 11th, 2003 10:51 PM

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

johngilbart June 12th, 2003 09:25 AM

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"



KenSchaefer June 12th, 2003 08:59 PM

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

johngilbart June 13th, 2003 06:03 PM

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?????

Imar June 13th, 2003 06:09 PM

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


All times are GMT -4. The time now is 06:35 PM.

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