Re: Connecting to a SQL Server 2005 DB on a Remote Web Server

Mike Webb wrote:
I have an existing VB6 app. that talks to an Access MDB over a LAN
and works fine.

I want to upgrade the Access MDB to SQL Server 2005 and move it to a
remote Web Server to be used in e-commerce with ASP.NET BUT still
access the DB from the Back Office VB6 app.

So far I have not been able to connect to the SQL Server DB and have
tried many connection string combos.

1st Can this ACTUALLY be done - or should I use an alternative
architecture? 2nd If so what should the connection string look like?

This should work (you may need to turn on the tcp/ip stuff in SQL Server
.... check with a sqlserver group for details):
Connect via an IP address:

"Provider=sqloledb;Data Source=190.190.200.100,1433;Network
Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source
is the port to use (1433 is the default))

If you've installed the SQL Native Client on the machine, then:
COM applications, all other then .Net applications

Use the SQL Native Client if you are accessing an SQL Server 2005 and
need the new features of SQL Server 2005 such as MARS, encryption, XML
data type etc. Continue use your current provider (OLE DB / ODBC through
the MDAC package) if you are not connecting to an SQL Server 2005
(that's quite obvious eh..) or if you are connecting to an SQL Server
2005 but are not using any of the new SQL Server 2005 features.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages

  • RE: How do I configure Analysis Services for Excel 2003 users?
    ... they had had Office 2003 installed AFTER SQL Server. ... > I have been preparing a set of cubes in Analysis Services 2005 to be viewed ... > Excel reports have been designed as pivot tables based on AS cubes, ... > Surely it must be that the connection string is incorrect but I seem to have ...
    (microsoft.public.sqlserver.olap)
  • Re: Problem connecting to an SQL 2005 Express Instance in RANU mod
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... I'm using SQL2005 Express User Instance Feature. ... attaching an MDB file with the following connection string: ...
    (microsoft.public.sqlserver.connect)
  • asp.net 2.0 login control
    ... A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.] ... These problems left me stranded for days, then today, on another machine, I hit a different set of problems I kept getting the following "Failed to generate user instance of SQL Server due to a failure in starting the process for the user instance. ... (Basically this is a new option in the connection string that allows you to point out where the ..mdf file resides eliminating the need to create database in some SQL instance.) ... Beware that the windows account of the user that first creates the connection in this databaseless manner, becomes exclusive user for this file and no other windows account will be able to create a connection to this file. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: How can I trap an error on a "CommitTrans" command in VB6 using ADO and SQL Server 2000?
    ... always use SQLOLEDB for accessing SQL server rather than MSDASQL. ... Try creating a UDL file and using this for your connection string. ... > adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) ... > client-side cursor and still be able to trap such a critical error. ...
    (microsoft.public.vb.database.ado)
  • RE: SQL server does not allow remote connections error
    ... Server 2005 however another web application could connect to the SQL Server ... use the alias in your connection string to connect to your SQL Server. ... Microsoft Online Community Support ... where an initial response from the community or a Microsoft Support ...
    (microsoft.public.sqlserver.connect)