SRM w/ SQL on separate server

[timfort]

I'm having some trouble installing SRM 4 and getting the service to start. Here's the scenario:

vCenter Server is Windows 2003 x64 VM. SQL server (SQL1) is also Windows 2003 x64 VM. I have created a SQL database and 2 users. 1 is an AD user named SRMAdmin and the other is a SQL user named SRM. The SQLAdmin user is also a local admin on the vCenter server. The SRM user has dbo permissions on the SRM database.

I created a 32-bit DSN on the vCenter server and attempted to use SQL Authentication with the SRM user... the connection fails. When I use Windows Authentication with SRMAdmin account the DSN connection tests successfully. During SRM install, I use SRMAdmin as the vCenter account and SRM as the database user account. The installation continues until it attempts to start the SRM service (vmware-dr) and then it fails to start the service.

The install log has an entry about the SQL Native Client being unable to connect to the database.

Any ideas?

[TimOudin]

It sounds like you've done your homework, you mentioned some of the most obvious issues.

Thoughts:

1) Failure to connect to your database with a sql server account is an issue, troubleshoot that first (permissions, default schema, credentials?).

a) Verify you gave proper permissions to the sql server user account, dbo on the srm database AND on msdb for the duration of the installation

2) You did select the "SQL Native Client" as the driver for the 32-bit DSN, correct?

Tim Oudin

[timfort]

Tim,

Yes, I used the 32-bit SQL Native Client to create the DSN. Don't know if I mentioned this... may not matter, but this is SQL 2008.

When you say dbo on SRM db and msdb... when you say MSDB is that an account, role or default db? I'm no SQL Admin but I have setup plenty of SQL servers before and created databases, DSNs, etc... and never had problems like this.

I guess I could use the sa account to verify the SQL permissions aren't the issue?

I've never had a problem with the DSN connecting to the database when Windows Authentication works to test the connection... then again, I know the documentation says that you must use SQL Auth when connecting to SQL on a separate machine.

Thanks...

[bladeraptor]

Hi

I have built out this scenario on a number of occassions and not seen issues - but this is the route I have tended to follow

Create a domain user service account

Add that user to the SQL environment with Database Create privileges and when creating the SRM database select this account as the DBO

Use this account and hence Windows authentication when creating the DSN on the SRM server and ensure that the SRM service on the SRM server uses the service account to start up.

In my case as I am working in a demo environment that service account also had elevated privileges as an administrator for the local machines (sort of nullifying the use of the Database Creator privilege in SQL if you haven't removed the default administrators entry in the SQL security set up)

Windows Authentication provides an easier way of doing things and provides better auditing and is to my mind more compliant in terms of the use of a domain level service account as this approach has more visibility to the global reosurces assigned security and compliance responsibilities

Regards

Alex Tanner

[tobiashansen]

Check your security log on the SQL server for failure audits at the time you try to connect.

Depending on the result of these grant the permissions to log on as service / log on as application in local security policy on SQL server - or grant the account used Allowed to authenticate on the SQL server computer object securtiy settings.

[CHogan]

You must use SQL Server Authentication when the database is remote from the SRM server. If you use Windows authentication, the SRM service will fail to start in exactly the manner you describe.

The only time that it is supported to use Windows Authentication is when SRM & the database are installed on the same host.

This is exactly the same requirement when it comes to deploying vCenter. If vCenter & the database are on the same host, you can use sql auth or windows auth; when the database is remote, you must use sql server.

Revisit your config and see why sql server authentication failed. Once you address that, your install should be successful.

HTH

Cormac

[pauljawood · Accepted solution]

Hi,

I had a similar issues and it was as simple as putting the database server in mixed mode. Once that was done and the SQL services had been restarted I could connect through the DSN without any issues at all using a remote SQL instance.

----

---

If you found this helpful then please leave some points.

[Michelle_Laveri]

I understood vCenter4 DOES support Windows Authentication in SQL... but SRM 4.0 does not...

On the subject of Windows Authentication for SRM 4.0. I managed to get it working by installing SRM. When it gets to start the service - it fails. If you leave the install window open, you can go off to the Services MMC, and grant the SRM DB user account the "right to logon as service". When you toggle back to the install window, and retry it is successful. I've been run like this since SRM 4.0 GA without a problem... of course it's not "supported" and any repair or upgrade is likely to require this "Services" fix...

It amazes me that VMW still persists in using SQL Authenication for some its products. It's rather frowned upon in the corporate space, and regarded by MSFT as being for "backwards compatiablity" and for "legacy" apps. SQL Authentication doesn't require the SQL have a domain account in AD, as such the authentication is "untrusted"....

Regards

Mike Laverick

RTFM Education

http://www.rtfm-ed.co.uk

Author of the SRM Book:http://stores.lulu.com/rtfm

Free PDF or at-cost Hard Copy

[TimOudin]

As clearly mentioned, SQL Server authentication is the only supported method for SRM when it's on a separate server. I have built out the environment exactly as you describe on occasion and seen that Windows authentication appears to work. What I then found in the SQL Server logs was the Local Service account attempting to establish the connection with the SQL Server. Sorry for not being clearer on my last post, I think I was tired.

Tim Oudin

[timfort]

Ok, thank you all for contributing. Essentially you all touched on the issue, but Paul hit the nail on the head. I had to set the SQL server to allow Mixed Mode Authentication which IS clearly stated in the install guide, but it's not clearly delineated as a step by step procedure.

Once I set Mixed Mode Authentication and restarted SQL the SQL login for the DSN worked and SRM installed and the service started.

Thanks again...