Prev   Next

Back to the top of the FAQ

Q. How to setup SQL database on a different machine, not on the webserver itself. (Can also help with a W2K3 SP2 permissions issue)

A. Hopefully the following will help set this up. Configurations vary so widely it is not possible to document them all here. Sometimes patience is needed (!)

7/7/2005:
With W2K3 and SP1, two new groups have been added:
Distributed COM Users
IIS_WPG
When you impersonate an NT user when making the ODBC call (usually with an Administrator account), make sure this account is a member of these two groups.

You will need to use the SQLOLEDB driver, instead of the default SQL driver normally presented in the ODBC control panel. The SQLOLEDB driver will not be visible here, and it shouldn't be.

The SQLOLEDB driver is available in the MDAC (Microsoft Data Access Components) package.

Create an SQLOLEDB connection string (see below).
Driver{SQL Server};Server=ServerName;Database=databaseName;UID=sa;PWD=;


(Also see SQLOLEDB_connection_string). 

eg
ConnectionString="Provider=SQLOLEDB.1;Password=WebUser1;
Persist Security Info=True;User ID=WebUser1;
Initial Catalog=VideoQuota;Data Source=MMS-ITVMEDIA;
Integrated Security=SSPI"
Here is a recent working sample: 
Driver={SQL Server};SERVER=MACHINE_NAME;Provider=SQLOLEDB.1;
Password=user1;Persist Security Info=True;User ID=WebUser1;
Initial Catalog=CATALOG1;Data Source=MACHINE_NAME
And another: 
Driver={SQL Server};SERVER=servername;Persist Security Info=True;
Database=dbname;UID=userid;PWD=password
Carefully match up the parameters on your connection string with the above example.

You may need to set up the appropriate SQL user/pass to access the database, as well as an NT user/pass that matches and is good for both machines. Make sure your SQL account has permissions to access all the relevant tables and procedures etc.

How to get it right every time:

  • First, catch your connection string.
    The best way to do this is to create an ASP/ADO page on the webserver, that connects to and reads from your Database. Likely you have already done this in order to add/change usernames/passwords in your database from the web.
    If not however, there are many excellent resources to help get this setup, including www.wrox.com, this great article at 4guysfromrolla, www.asp101.com (especially this article on connection strings), aspAlliance.com, etc. and Microsoft articles! BEGINNERS will enjoy this article from WebMonkey's Jay Greenspan
    Also see http://www.connectionstrings.com/.
    If these don't help, then since you are using only ASP, ADO, SQL and these are all Microsoft products they will be able to fix you up, (probably for a Tech Support fee though).
    Even so, most of the bases are covered by referring to the format of the SQLOLEDB Connection string above (and below).
  • In the ODBC setup dialog, paste this connection string into the Text Box next to the Data Source button.
  • Use Standard Select to begin with.
  • Press the Table button, it should come up with a list of Tables in your database. This is the first hurdle to overcome. Should there be permission errors, try the "Impersonate NT User" Option, and check your SQL user/pass.
    Note also, that if you check the "Impersonate NT User" Option, the Test button may fail, however, the actual filter database access can succeed. Give it a try.
  • Fill out the username and password fields.
  • Press the Test button. Check and resolve any error messages.
  • Now try to access the protected directory via the web (http).
  • If it doesn't work perfectly, check the "Show reason in access denied" (Options dialog), and try again.
  • If this doesn't help, check the Application Event Log for clues. Perhaps the NT user you are impersonating does not have
    "Act as part of the Operating System" advanced user rights. If it doesn't then add them (if you are logged in under that account, logout/login or reboot to apply the changes). The same goes for the
    "Log on locally" privilege. Otherwise you will likely get "[1314]A required privilege is not held by >the client" when using the Test button. To add privileges: Control Panel, Administrative Tools, Local Security Policy, Local Policies, User Rights Assignment. (Phew! knew where it was in NT4, took some finding in W2K!).
  • If this doesn't help, open the SQL Profiler, and check the SQL is getting through to the server and correctly executing.
  • If it is still not working, it is time for MARIO to help us...
That being said, Cory has some additional insight for his setup:

Here's the final version:

Driver={SQL Server};SERVER=206.xxx.234.xxx;Persist Security Info=True;Database=dev;UID=xx;PWD=xxxxx;

A few things I experienced went against the FAQ page, and I thought I'd make note of:

1. Your FAQ #94 needs an equal sign after the "driver" in the first example, as in "Driver={SQL Server};".

2. SQLOLEDB would never work, even though several combinations of the connection string worked in ASP.

3. It wasn't clear that integrated NT security was not required.

4. While I was trying to get integrated NT security to work, I kept getting "A required privilege is not held by the client", even though the user I was using had both "act as operating system" and "log on locally rights", and also had full control of all databases, which was all set up prior to installing AuthentiX.

Thanks Cory!

Back to the top of the FAQ

Prev   Next