redho home | products | services

Programming Forums


Community for Java, PHP, Perl C, ASP and Python programmers
Monday 15 October 2018 20:59

Ask your IT question here

What should my connection string look like?



 
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.    Programming Forums -> ASP in depth
View previous topic :: View next topic  
Author Message
Tom
Guest Programmer




What should my connection string look like?
Reply with quote
 
Guest Programmer





I've been preaching for years that DSNs cause unnecessary overhead and extra maintenance tasks. And all this time, the majority of Microsoft's code samples have displayed the use of DSNs. Now, Microsoft has deprecated ODBC, and is finally backing me up. So, whenever possible, use a native OLEDB provider, rather than a DSN.

The overhead caused by DSNs is clear and testable. Many people scratch their heads about why I insist that DSNs make code harder to maintain. Well, unless you can log into the ISP's machine with remote software (which is typically only allowed when you're leasing entire servers to yourself), it is not trivial to make changes to existing DSNs, or add new ones. You need to have your ISP manage your DSN(s), which certainly takes time, and sometimes costs money (depending on the host). Granted, some hosts have pretty "control panel" type applications that help you do this. But it still moves one of your programming tasks to a location outside of your development environment.

Also, you can only manage so many DSNs reasonably (see KB #252475). Using a DSN-less connection (for example, those found in this article), you can avoid this hassle *and* make your data access faster.

SQL Server

Using SQL Server Authentication:

Quote:
<%
cst = "Provider=SQLOLEDB;" & _
"Data Source=<x.x.x.x>;" & _
"Initial Catalog=<dbname>;" & _
"Network=DBMSSOCN;" & _
"User Id=<uid>;" & _
"Password=<pwd>"

' // Use of Network=DBMSSOCN is to avoid Named Pipes errors;
' // see Article #2082 for more details

set conn = CreateObject("ADODB.Connection")
conn.open cst
%>


To use SQL Server authentication, SQL Server must be set up in 'mixed mode' (both SQL Server and Windows Authentication) and you must have a SQL Server login with appropriate permissions on the database(s) you are connecting to. To set SQL Server to mixed mode, open Enterprise Manager, right-click the relevant server registration, and move to the security tab. Under authentication, make sure that "SQL Server and Windows" is checked. (You should also take this opportunity to make sure that sa has a strong—and definitely not a blank—password).

Using Windows Authentication:

Quote:
<%
cst = "Provider=SQLOLEDB;" & _
"Data Source=<x.x.x.x>;" & _
"Initial Catalog=<dbname>;" & _
"Integrated Security=SSPI"

set conn = CreateObject("ADODB.Connection")
conn.open cst
%>


Note that to use Windows Authentication, IUSR_<machineName> must be in the domain, and given proper access to the SQL Server; or, you must disable anonymous access on the site / application - which will allow IIS to pass the users' credentials to SQL Server. Not doing either of these things will result in the following error:

Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user '<machineName>\IUSR_<machineName>'. Reason:
Not associated with a trusted SQL Server connection.

For information about configuring SQL Server for access through IIS, see KB #247931.

If you are using an IP address or a server name that should be resolved through DNS or over the Internet, you'll want to make sure that TCP/IP is enabled. Go to Start / Programs / Microsoft SQL Server, open the Client Network Utility, and on the General tab, make sure TCP/IP is at the top of the list on the right hand side.

If you are trying to connect to a named instance, you can use the following format:

Data Source = <server/ip>\<instancename>;

If you are running SQL Server or MSDE on the same machine as your ASP pages, you are probably tempted to use the hostname "localhost." This doesn't always work, due to different configuration issues, so try (local), 127.0.0.1, the actual host name, or simply a period (".").

Some people have asked about the difference between SQLOLEDB and SQLOLEDB.1. The former is the version-independent provider name, while the latter uses a specific version (.1, obviously). It is recommended that, unless you need to use the specific .1 version, you use the version-independent provider name—if for no other reason, to ensure that your connection string works on every machine you port it to (some might not have the .1 update).

SQL Server Analysis Services

Quote:
<%
strASConn = "PROVIDER=MSOLAP;" & _
"DATA SOURCE=<ip>;" & _
"INITIAL CATALOG=<dbname>"

or

strASConn = "PROVIDER=MSOLAP.2;" & _
"DATA SOURCE=<ip>;" & _
"INITIAL CATALOG=<dbname>"

' and then

set ASCellset = CreateObject("ADOMD.Cellset")
ASCellset.ActiveConnection = strASConn

or

set ASCatalog = CreateObject("ADOMD.Catalog")
ASCatalog.ActiveConnection = strASConn
%>


Oracle

If you are using the Microsoft OLEDB provider for Oracle:

Quote:
<%
cst = "Provider=MSDAORA;" & _
"Data Source=<server>.<dbname>;" & _
"User ID=<uid>;" & _
"Password=<pwd>;"

set conn = CreateObject("ADODB.Connection")
conn.open cst
%>


If you are using the Oracle OLEDB Provider (which you can download after registering here):

<%
cst = "Provider=OraOLEDB.Oracle;" & _
"Server=<server>;" & _
"Data Source=<dbname>;" & _
"User ID=<uid>;" & _
"Password=<pwd>"

set conn = CreateObject("ADODB.Connection")
conn.open cst
%>

MySQL

MySQL.com used to have a download available for an OLEDB provider to MySQL (MyOLEDB):

Quote:
<%
cst = "Provider=MySQLProv;" & _
"Data Source=<x.x.x.x>;" & _
"Database=<dbname>;" & _
"User Id=<uid>;" & _
"Password=<pwd>"
%>


However, it has gone missing in recent months. If you don't have the OLEDB provider, you will need to use ODBC. You can try these connection strings (depending on whether you have MySQL ODBC or MyODBC drivers installed):

<%
cst = "Driver={MySQL};" & _
"Server=<x.x.x.x>;" & _
"Database=<dbname>;" & _
"Uid=<uid>;" & _
"Pwd=<pwd>"

cst = "Driver={MySQL ODBC 3.51 Driver};" & _
"Server=<x.x.x.x>;" & _
"Database=<dbname>;" & _
"Uid=<uid>;" & _
"Pwd=<pwd>;"
%>

You can download Windows MyODBC drivers here.

DB2 and AS/400

Please see the following KB articles for Configuring Data Sources for the Microsoft OLE DB Provider for DB2 and AS/400 and VSAM.

Microsoft Access

Quote:
<%
cst = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("/<pathtofile.mdb>")

set conn = CreateObject("ADODB.Connection")
conn.open cst
%>


If you don't have the latest version of JET 4.0 installed, consider installing it (see Article #2342). You also might find that with newer versions of MDAC, you get this error:

Provider error '80040e4d'
Authentication failed.

If you can't install the latest JET driver, or you are having issues getting it to work, you can fall back to the native ODBC driver for Access (however note that it is deprecated):

<%
cst = "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath("/<pathtofile.mdb>")

set conn = CreateObject("ADODB.Connection")
conn.open cst
%>

If your Access database is on another server, you will need to ensure that Jet 4.0 is installed on the remote server, and that you know the *local* location of the MDB file on that server. Once you have those two vital pieces, you can use a connection string like this (see Article #2168 for more details):

<%
cst = "Provider=MS Remote;" &_
"Remote Server=http://<x.x.x.x>;" &_
"Remote Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=c:\inetpub\wwwroot\file1.mdb;"
set conn = CreateObject("ADODB.Connection")
conn.open cst
%>

(And I feel sorry for you. Access is not really up to this task, as I'm sure you'll quickly learn.)

In each case, of course, filling in the <variables> with the proper values.

Regarding using JET over ODBC, according to KB #222135:

"When running Microsoft Jet in an IIS environment, it is recommended that you use the native Jet OLE DB Provider in place of the Microsoft Access ODBC driver. The Microsoft Access ODBC driver (Jet ODBC driver) can have stability issues due to the version of Visual Basic for Applications that is invoked because the version is not thread safe. As a result, when multiple concurrent users make requests of a Microsoft Access database, unpredictable results may occur. The native Jet OLE DB Provider includes fixes and enhancements for stability, performance, and thread pooling (including calling a thread-safe version of Visual Basic for Applications)."

If you are using a Workgroup file, you might have seen this error:

Microsoft JET Database Engine (0x80040E4D)
Cannot start your application. The workgroup information file is missing or opened exclusively by another user.

To get around this, you need to properly specify your workgroup file location, and pass a valid username and password, as follows:

Quote:
<%
cst = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("/<pathtofile.mdb>") & ";" & _
"Jet OLEDB:System Database=<wg_file_name>.mdw"

set conn = CreateObject("ADODB.Connection")
conn.open cst, "Username", "Password"
%>



For a more exhaustive list of connection string types, see http://www.carlprothman.net/Default.aspx?tabid=81, where Carl Prothman has compiled several different techniques of connecting to just about any data source -- from DB2 to Sybase to Visual FoxPro...

You can also see Jimmy Engström'shttp://www.connectionstrings.com/ for a variety of connection strings.

Reply with quote
 
Page 1 of 1
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.    Programming Forums -> ASP in depth


Dubai Forums - Expat Help | Vegan Forum | Java Programming | 3d Design Resources | 3d Forum | 3D Jobs | 3D Textures | Paris Forum | Europe Forum
Dubai Classifieds | Dubai Property | Jobs in Dubai | Free London Classifieds | Jobs in London UK

High Quality, Custom 3d animation and Web Design solutions Royal Quality Web Hosting Services Vegetarian and Animal Rights news

© 2018 RedHo