I came across this error/issue when attempting to install System Center Service Manager in my lab. My architecture/design is to have 3 servers:
- One for the Service Manager Management Server along with its database
- One for the Service Manager Warehouse along with its database and SQL Reporting Services, and SQL Analysis Services
- One for SharePoint for the Self-Service Portal
So lets start with my Virtual Machine configuration. For each of the VMs, I have given it a maximum of 8GB of RAM, along with 4 virtual CPUs.
For the Service Manager Management Server, since I will its database locally, I installed SQL Server 2012 SP1 with the following features; namely: Database Engine Services, Full-Text and Semantic Extraction for Search, and the complete Management Tools.
Note the SQL Server Instance name that I used (in this example SCSMMSSQLSERVER). Generally, since I monitor my environment with Operations Manager (SCOM), I don’t like to use the default naming for SQL Instances, especially when I install SQL Server locally for each System Center product. This means that, if I were to use the default SQL instance naming (MSSQLSERVER), I would end up with a lot of instances labelled the same thing in the Operations Manager (SCOM) console. this doesn’t make it easy to identify which instance is potentially having an issue.
So generally, I like to use the product name and append “SQLSERVER”. For example, the SQL Server Instance name for SCOM would be “SCOM” + “SQLSERVER” = “SCOMSQLSERVER”. In the case of Service Manager, there are 2 SQL Instances that are used, one for the Service Manager database, and another for the Service Manager warehouse database. Therefore, I wouldn’t just name each instance “SCSM” + “SQLSERVER”, as that would cause a duplicate instance name (see the paragraph above about my ramblings for unique naming). So, logically I need to add something else to the SQL Server Instance name to identify between the Service Manager Management Server, and the Warehouse Server. Hence the “SCSM” + “MS” (Management Server) + “SQLSERVER”. I followed this same naming convention for the Warehouse SQL Server Instance (i.e. “SCSM” + “WH” + “SQLSERVER”).
OK, enough of the back-story. When I attempted to install System Center Service Manager – Management Server, in particular on the “Configure the Service Manager database” screen, I encountered the following error: “Access to the SQL Server instance <Server Name\Instance Name> was denied.”
I even attempted to use the Full Qualified Domain Name (FQDN) for the server name, but the same error occurs.
Of interesting note is SQL Server instance that is “discovered”. It’s odd that it says “SCSMDefault”, because if you look at my SQL Server installation, that’s not the instance name.
That doesn’t make any sense. Why would the installation wizard, when given the correct server name, be unable to discover the correct (and in this case, only) instance configured on the server? Additionally, it gives an error about access being denied; although I know for certain that I am using an Administrator account (which has been configured with Local Admin on the server, and SysAdmin on the SQL Server installation).
I spent countless days searching the web for any articles on System Center Service Manager, database requirements, and the specific error that was generated; all to no avail. I read articles that mention things such as: firewalls, SQL Named Pipes, SQL TCP/IP, Dynamic Ports vs. Static Ports, SQL Allow Updates, using underscores in database names, SQL remote connections, etc. All of these did not resolve the issue. I even tried installing SQL Reporting Services and SQL Analysis Services (since the documentation isn’t specifically clear which database; either the Service Manager database, or the Data Warehouse database). And yet I still encountered the error.
What made matters even more confusing and difficult to troubleshoot, was the fact that the installation for the Data Warehouse (using the same naming convention, and in fact had the same number of characters in the SQL Server instance name), did not show the same error!
Then I came across this article (http://stackoverflow.com/questions/5260650/max-length-of-sql-server-instance-name) that mentioned a limitation to the Instance Name length. However, the article referenced a different version of SQL Server than I was using. Note: I am using SQL Server 2012 SP1. So, I tried finding “SQL Server Books Online” for SQL Server 2012, and although it does exist (found here: http://technet.microsoft.com/en-us/library/ms130214.aspx), I could find any information on Instance Naming Configuration. So, based on reference to older versions of SQL Server (see http://msdn.microsoft.com/en-us/library/ms143531(v=sql.120).aspx), “Instance names are limited to 16 characters”.
So armed with this information, I went back and reviewed my Instance Name. Hmmm, “S-C-S-M-M-S-S-Q-L-S-E-R-V-E-R” is only 15 characters, so it “fits”, so what gives? Well, not being a Database Administrator (DBA), I don’t really know. I figure that the Server Name might somehow tie in with length restrictions/SQL references.
So, I decided to uninstall/re-install SQL Server on the Service Manager Management Server with a shorter SQL Server Instance Name. This time, instead of “SCSMMSSQLSERVER”, I used “SCSMMS”. Then I re-ran the System Center Service Manager Management Server installation. When I got to the “Configure the Service Manager database” screen, the installation wizard was able to successfully connect to the SQL Server (even though it IS on the LocalHost), and this time it was able to correctly identify and access the SQL Server Instance!
I’m still not 100% sure why this is, and I have not been able to find any official documentation from Microsoft (either in relation to SQL Server 2012 SP1 or System Center Service Manager 2012 R2). But hopefully my experience (and potential resolution, or at least work-around), will be of some help to someone else.
MiCloud 