Accessing SQL on a different port number (classic ASP connection string)

To update the native client on the web server to the most recent public version, go here:

http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2567714

Check the box for "2008R2_SP1_SNAC_CU2_2567714_10_50_2772_x86" if your web server is an X86 machine, and "2008R2_SP1_SNAC_CU2_2567714_10_50_2772_x64" if it is 64-bit. Enter your e-mail address, fill in the captcha, hit "Request hotfix" and the download link will be e-mailed to you. Download it to the web server, run the installer (using the password sent to you in the e-mail) and restart the web server. Now try your original connection string again.

EDIT

Adding some other sample connection strings that I have used in the past (I've been a classic ASP guy since the 90s, founding and writing 95% of the content on aspfaq.com, including this article on connection strings). As requested in a comment to the original question.

Note that "srv" could be IP address, or localhost, or servername, or . and it could be followed by \INSTANCE_NAME (e.g. \SQLEXPRESS). I would try them in this order, and pay attention to the parameter names as well as the values - you seem to mixing modern parameter names (e.g. Provider) with less modern ones (e.g. Database).

Provider=SQLNCLI10;Data Source=srv;Initial Catalog=db;User ID=user;Password=pwd;

Provider=SQLOLEDB.1;Data Source=srv;Initial Catalog=db;User ID=user;Password=pwd;

Provider=SQLOLEDB;Data Source=srv;Initial Catalog=db;User ID=user;Password=pwd;

Driver={SQL Server};Server=srv;Database=db;UID=user;PWD=pwd;

Sometimes if you are having issues with Named Pipes or Shared Memory it can help to enforce TCP/IP by adding the following parameter:

NETWORK=DBMSSOCN;

You can get a lot more information over at Carl Prothman's site or ConnectionStrings.com. They no longer cater to classic ASP specifically, for obvious reasons, but you can still glean a lot of information from the samples they provide for other languages.

SQL Server authenticates your login at the server level. Then it tries to open the database you've asked to connect to. At this point, you need to either map a database-level user to the server-level login, or use a server-level login that inherently has sufficient privileges to use the database.

A simple (but not secure) way to demonstrate this using SQL Server Authentication:

USE master;
GO
CREATE LOGIN foo
  WITH PASSWORD = 'bar', CHECK_POLICY = OFF;
GO
USE BRD;
GO
CREATE USER foo FROM LOGIN foo;
GO
EXEC sp_addrolemember N'db_owner', N'foo';
GO

Now in your ASP connection string, use:

objConn.ConnectionString = "Provider=SQLNCLI;Data Source=XPSI7\SQLEXPRESS;Initial Catalog=BRD;User ID=foo;Password=bar;"

You can also map whatever login you're using to a database user simply doing:

USE BRD;
GO
CREATE USER [YourDomain\IIS_IUSRwhatever] 
  FROM LOGIN [YourDomain\IIS_IUSRwhatever];
GO

That will grant them access to the database, but it will be up to you to decide what permissions to grant. This assumes that you are allowing anonymous access to the web server; if IIS is challenging and accepting Windows authentication, you'll need to do the above for the user(s) that will be submitting their credentials. In either case you should be able to continue using the connection string you have now.