Separating web service and SQL onto different servers?

[MartinCNagel]

I'm moving from a standalone environment to a hosted (shared) environment. In that environment, the server hosting team has IIS located on the web servers and only SQL server on the high-performance SQL boxes. IIS is not allowed to be run on the SQL servers. Is there a way to install and configure the Vault server components so that the users connect to the IIS service on the web server, and it retrieves the data from the SQL server?

[lbauer]

Yes, you can certainly put the Vault service on one machine and have it connect to a SQL Server database on a different machine.

To do this, restore a copy of your database to the new SQL Server machine. Then, install Vault on the Web server. If you are using Windows authentication for SQL Server, then use a custom account (domain account) for the IIS process model portion of the installation.

If you wish to use SQL Server authentication, you can choose that in the SQL server setup portion of the installation. During the installation, enter the SA credentials in the SQL server setup page. The installer will use those credentials to create the sgvaultuser account, which the Vault server will use to communicate with the database.

[RGood]

Yes, you can certainly put the Vault service on one machine and have it connect to a SQL Server database on a different machine.

To do this, restore a copy of your database to the new SQL Server machine. Then, install Vault on the Web server. If you are using Windows authentication for SQL Server, then use a custom account (domain account) for the IIS process model portion of the installation.

If you wish to use SQL Server authentication, you can choose that in the SQL server setup portion of the installation. During the installation, enter the SA credentials in the SQL server setup page. The installer will use those credentials to create the sgvaultuser account, which the Vault server will use to communicate with the database.

I upgraded the Vault server in place from v3.5.1 to 5.0.3 in several steps after reading articles in the forum. The vault server is running on Windows Server 2003 and the database is SQL 2000 on the same server.

I now want to move the Vault server and database to a newer setup where the database is SQL2008 on a physical server running Windows Server 2008 R2 and the web service will run on a virtual server running Windows Server 2008 SP2. Both servers are in the same domain, and I created a domain account to be used by the Vault Server.

I backed up both the sgmaster and sgvault databases from the old SQL server. Created new databases on the new SQL server and restored the content of the old databases to the new server. I added the new domain account to the SQL server and gave it db_owner privileges in both databases. I logged in as domain admin on the VM and ran the install, putting the domain account in on the step that asked for "Choose IIS Process User". I put in my existing Vault admin password when prompted. When prompted for the SQL server, I put in the name of the physical machine as SQL is installed there as the default instance.

I got a message "SQL Connection failed. Unable to connect to SQL. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

[now having problems with scrolling of this window - will respond with more.]

[RGood]

Looking into the situation further, I went as far as giving the anonymous account db_owner privileges in both databases, but it failed again. Using SQL profiling, I could see that the install was attempting to drop and re-add the sgmaster database. I was not willing to give the anonymous user permission to drop and add databases, so I stopped the work there.

Do you know why the installation was attempting to use the anonymous user rather than the configured domain users?

Do you know why the installation was attempting to drop and re-add the sgmaster database that had been created by the same version of the Vault Server (albeit on SQL2000)?

Thanks for your assistance,
Bob G.

[lbauer]

I'm not sure why the installer did this, although it might be related to this:

Created new databases on the new SQL server and restored the content of the old databases to the new server. I added the new domain account to the SQL server and gave it db_owner privileges in both databases.

I'd suggest starting over by uninstalling Vault and dropping the databases and the database user you created.

You should be able to just restore a backup of the sgvault and sgmaster databases to SQL Server 2008 without creating new databases. Then run the Vault installer, enter the Windows account you want to use in the IIS Process Model part of the installation and let the installer create the login and roles for your Windows domain account in SQL Server. You should be logged in as a domain admin so that the installer will use your credentials to do this.

If this still doesn't work, send me a copy of the Vault Server installation log. It's called Vault_install.log and is in your user's temp directory on the server machine, %temp%.

[RGood]

Since Vault failed to install previously, there was nothing to uninstall.

I deleted the sgmaster and sgvault databases from the SQL2008 db server, then restored them to the SQL2008 database from a backup I made earlier this evening on the SQL2000 server.

I logged in to the server VM where the web services were to be installed and entered the name of the domain account to be used by the web services.

After entering the Vault admin password and selecting the name of the SQL server and to use Windows auth, I again got the message box with the text above: "SQL Connection failed. Unable to connect to SQL. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

I've emailed you the contents of vault_install.log.

[lbauer]

I'm not sure we got your e-mail. Could you send the installation log again. Send the log to support at sourcegear.com, Attn: Linda. Please include a link to this forum post.

[RGood]

Per request, sent email on 3/10 at 5:54 PM EST directly to support at sourcegear.com Attn: Linda

[lbauer]

The install log isn't even showing the domain account you are trying to use. I sent you an e-mail from our help system suggesting we set up a remote assistance session.

[RGood]

Keeping enough history here that some can benefit from this later...

Per your request during our remote session (which duplicated my previous results), I confirmed that the domain account does have access from the web server VM to the SQL db on the other server.

[lbauer]

Did you have your domain user log into the Vault Server machine to create a local profile?

[RGood]

For clarity (and to recap history), let's call the servers WebServer and HostServer. HostServer is the physical machine running SQL 2008 on Windows Server 2008 R2 64 bit. WebServer is the virtual machine that has IIS on Windows Server 2008 SP2 32 bit. Both HostServer and WebServer are members of the domain.

I've logged in to WebServer with the domain account, and I've confirmed that when I do, there is a profile there for the account. I have also confirmed that when I'm logged in to WebServer with the domain account, I can run a WinForms application that connects to the database on HostServer using Windows authentication.

After having made these confirmations, I did a little more checking and found that though the folders in C:\inetpub\wwwroot were deleted in the previous install rollback, the virtual folders and the application pool in IIS were not. I deleted the virtual folders and the app pool and re-ran the install from WebServer while logged in there as the domain admin. I got the same results as previously.