tyy8
asked on
ASP database connection
I have an ASP/COM site that is setup to connect to a sql7 database on another server, as far as I can tell - the COM object has all the right info. going thru it (server's IP address, databasename, etc..) - but it doesn't seem to connect. Does sql7 need to be on the ASP site's machine. I'm just wondering if maybe the problem is related to support files and not the ASP/COM pages & code.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It is a DSNless connection - this site already works on another server - so I know that the connection string is fine. The web server doesn't have sql7 on it.
If you install the SQL Server 7 management console (MMC) only can you then connect to the SQL Server. Is the server using windows authentication or are you using a plain user-account like sa (not recommended).
CJ
CJ
Didn't write that properly. Can you connect from another machine to the SQL Server (MMC is just a tool to very).
CJ
CJ
Does the IUSR_machine account from the web server have the right permissions to access the remote SQL server? Are you setting the connection string with a valid username/password combo for that SQL server?
If the remote SQL server doesn't recognize that IUSR_machine account/domain or account/password in the DSN/connection string as an authorized user, it won't connect.
If the remote SQL server doesn't recognize that IUSR_machine account/domain or account/password in the DSN/connection string as an authorized user, it won't connect.
ASKER
I'm using windows authentication, not the IUSR_machine account.
Here are some connection string examples
This page contains sample ADO connection strings for ODBC DSN / DSN-Less, OLE DB Providers, Remote Data Services (RDS), MS Remote, and MS DataShape.
Also included are ADO.NET connection strings for each the .NET Data Providers (MySQL, ODBC, OLE DB, Oracle, and SQL Server).
These sample connection strings are compiled by Carl Prothman, a Microsoft Visual Basic MVP. If you have an ADO connection string that is not listed below, please email it to him and it will add to this page.
Table of Contents
.NET Data Provider Connections MySQL .NET Native Provider
ODBC .NET Data Provider
OLE DB .NET Data Provider
Oracle .NET Data Provider
SQL Server .NET Data Provider
ODBC DSN Connections DSN
File DSN
ODBC DSN-Less Connections ODBC Driver for Access
ODBC Driver for AS/400
ODBC Driver for dBASE
ODBC Driver for Excel
ODBC Driver for Informix
ODBC Driver for MySQL
ODBC Driver for Oracle
ODBC Driver for Paradox
ODBC Driver for SQL Server
ODBC Driver for Sybase
ODBC Driver for Sybase SQL Anywhere
ODBC Driver for Text
ODBC Driver for Teradata
ODBC Driver for Visual FoxPro
OLE DB Data Link Connections Data Link File (UDL)
OLE DB Data Provider Connections OLE DB Provider for Active Directory Service
OLE DB Provider for AS/400 (from IBM)
OLE DB Provider for AS/400 and VSAM (from Microsoft)
OLE DB Provider for Commerce Server
OLE DB Provider for DB2
OLE DB Provider for DTS Packages
OLE DB Provider for Exchange
OLD DB Provider for Internet Publishing
OLE DB Provider for Index Server
OLE DB Provider for Microsoft Jet
OLE DB Provider for Microsoft Project
OLE DB Provider for MySQL
OLE DB Provider for ODBC Databases
OLE DB Provider for OLAP Services
OLE DB Provider for Oracle (from Microsoft)
OLE DB Provider for Oracle (from Oracle)
OLE DB Provider for Pervasive
OLE DB Provider for Simple Provider
OLE DB Provider for SQLBase
OLE DB Provider for SQL Server
OLE DB Provider for SQL Server via SQLXMLOLEDB
OLE DB Provider for Sybase Adaptive Server Anywhere
OLE DB Provider for Sybase Adaptive Server Enterprise
OLE DB Provider for UniData and UniVerse
OLE DB Provider for Visual FoxPro
ADO URL Connections ADO Recordset
Remote Data Service (RDS) Connections RDS Data Control - Connect Property
RDS Data Control - URL Property
MS Remote Provider Connections MS Remote - Access (Jet)
MS Remote - SQL Server
Data Shape Provider Connections MS DataShape - SQL Server
ODBC DSN Connections
Using an ODBC DSN (Data Source Name) is a two step process.
1) You must first create the DSN via the "ODBC Data Source Administrator"
program found in your computer's Control Panel (or Administrative Tools
menu in Windows 2000). Make sure to create a SYSTEM DSN (not a USER
DSN) when using ASP. You can also create the DSN via Visual Basic code.
2) Then use the following connection string - with your own DSN
name of course.
ODBC - DSN
oConn.Open "DSN=mySystemDSN;" & _
"Uid=myUsername;" & _
"Pwd=myPassword;
ODBC - File DSN
oConn.Open "FILEDSN=c:\somepath\mydb.
"Uid=myUsername;" & _
"Pwd=myPassword;"
For more information, see: About ODBC data sources and
How to Use File DSNs and DSN-less Connections
Note: The problem with DSN is that Users can (and will) modify or delete them
by mistake, then your program won't work so well. So it's better to use a DSN-Less
or OLE DB Provider connection string - with a Trusted Connection if possible!
ODBC DSN-Less Connections
ODBC Driver for Access
For Standard Security:
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;
"Uid=admin;" & _
"Pwd=;"
If you are using a Workgroup (System database):
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;
"SystemDB=c:\somepath\mydb
"myUsername", "myPassword"
If want to open up the MDB exclusively:
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;
"Exclusive=1;" & _
"Uid=admin;" & _
"Pwd=;"
If MDB is located on a Network Share:
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=\\myServer\myShare\my
"Uid=admin;" & _
"Pwd=;"
If MDB is located on a remote machine:
- Use an ADO URL with a remote ASP web page
- Or use a "MS Remote" or RDS connection string
- Or use an XML Web Service via SOAP Toolkit or .NET
- Or upgrade to SQL Server and use an IP connection string
If you don't know the path to the MDB (using ASP)
<% ' ASP server-side code
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=" & Server.MapPath(".") & "\myDb.mdb;" & _
"Uid=admin;" & _
"Pwd=;"
%>
This assumes the MDB is in the same directory where the ASP page is running.
Also make sure this directory has Write permissions for the user account.
If you don't know the path to the MDB (using VB)
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=" & App.Path & "\myDb.mdb;" & _
"Uid=admin;" & _
"Pwd=;"
This assumes the MDB is in the same directory where the application is running.
For more information, see: Microsoft Access Driver Programming Considerations
To view Microsoft KB articles related to Microsoft Access Driver, click here
ODBC Driver for AS/400 (from IBM)
oConn.Open "Driver={Client Access ODBC Driver (32-bit)};" & _
"System=myAS400;" & _
"Uid=myUsername;" & _
"Pwd=myPassword;"
For more information, see: A Fast Path to AS/400 Client/Server
ODBC Driver for dBASE
oConn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
"DriverID=277;" & _
"Dbq=c:\somepath;"
Then specify the filename in the SQL statement:
oRs.Open "Select * From user.dbf", oConn, , ,adCmdText
Note: MDAC 2.1 (or greater) requires the Borland Database Engine (BDE) to
update dBase DBF files. (Q238431).
For more information, see: dBASE Driver Programming Considerations
To view Microsoft KB articles related to Microsoft dBASE Driver, click here
ODBC Driver for Excel
oConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
"DriverId=790;" & _
"Dbq=c:\somepath\mySpreads
"DefaultDir=c:\somepath;"
For more information, see: Microsoft Excel Driver Programming Considerations
To view Microsoft KB articles related to Microsoft Excel Driver, click here
ODBC Driver for Informix
If using INFORMIX 3.30 ODBC Driver
oConn.Open "Dsn='';" & _
"Driver={INFORMIX 3.30 32 BIT};" & _
"Host=myHostname;" & _
"Server=myServerName;" & _
"Service=myServiceName;" & _
"Protocol=olsoctcp;" & _
"Database=myDbName;" & _
"UID=myUsername;" & _
"PWD=myPassword;" & _
' Or
oConn.Open "Dsn=myDsn;" & _
"Host=myHostname;" & _
"Server=myServerName;" & _
"Service=myServiceName;" & _
"Protocol=onsoctcp;" & _
"Database=myDbName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword;"
If using INFORMIX-CLI 2.5 ODBC Driver
oConn.Open "Driver={Informix-CLI 2.5 (32 Bit)};" & _
"Server=myServerName;" & _
"Database=myDbName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword;" & _
For more information, see: Informix Developer Zone, Connection to ODBC Data Source,
ODBC Driver for MySQL (via MyODBC)
To connect to a local database
oConn.Open "Driver={mySQL};" & _
"Server=MyServerName;" & _
"Option=16834;" & _
"Database=mydb;"
To connect to a remote database
oConn.Open "Driver={mySQL};" & _
"Server=db1.database.com;"
"Port=3306;" & _
"Option=131072;" & _
"Stmt=;" & _
"Database=mydb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword;"
For more information, see: Programs Known to Work with MyODBC
ODBC Driver for Oracle
For the current Oracle ODBC Driver from Microsoft:
oConn.Open "Driver={Microsoft ODBC for Oracle};" & _
"Server=OracleServer.world
"Uid=myUsername;" & _
"Pwd=myPassword;"
For the older Oracle ODBC Driver from Microsoft:
oConn.Open "Driver={Microsoft ODBC Driver for Oracle};" & _
"ConnectString=OracleServe
"Uid=myUsername;" & _
"Pwd=myPassword;"
For more information, see: Connection String Format and Attributes
To view Microsoft KB articles related to Microsoft ODBC for Oracle, click here
ODBC Driver for Paradox
oConn.Open "Driver={Microsoft Paradox Driver (*.db)};" & _
"DriverID=538;" & _
"Fil=Paradox 5.X;" & _
"DefaultDir=c:\dbpath\;" & _
"Dbq=c:\dbpath\;" & _
"CollatingSequence=ASCII;"
Note: MDAC 2.1 (or greater) requires the Borland Database Engine (BDE)
to update Paradox ISAM fDBF files. (Q230126).
For more information, see: Paradox Driver Programming Considerations
To view Microsoft KB articles related to Microsoft Paradox Driver, click here
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 Prompt user for username and password
oConn.Properties("Prompt")
oConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"DataBase=myDatabaseName;"
To connect to SQL Server running on the same computer:
oConn.Open "Driver={SQL Server};" & _
"Server=(local);" & _
"Database=myDatabaseName;"
"Uid=myUsername;" & _
"Pwd=myPassword;"
To connect to SQL Server running on a remote computer (via an IP address):
oConn.Open "Driver={SQL Server};" & _
"Server=xxx.xxx.xxx.xxx;" & _
"Address=xxx.xxx.xxx.xxx,1
"Network=DBMSSOCN;" & _
"Database=myDatabaseName;"
"Uid=myUsername;" & _
"Pwd=myPassword;"
Where:
- xxx.xxx.xxx.xxx is an IP address
- 1433 is the default port number for SQL Server.
- "Network=DBMSSOCN" tells ODBC to use TCP/IP rather than Named
Pipes (Q238949)
For more information, see: SQLDriverConnect (ODBC)
To view Microsoft KB articles related to ODBC Driver for SQL Server, click here