SQL Server 2014 Developer Edition - Installation Best Practices

Question

SQL Server 2014 Developer Edition - Installation Best Practices

Scott In Sydney

Hall of Fame

Points: 3331
More actions
August 3, 2017 at 10:14 pm

#323040

I've installed SQL Server 2014 Developer Edition several times on my Win7 desktop. The installation is just for POC, development, testing purposes.
Key points re: the installation:
* Installation #1 used the default users: NT Service\SQLAgent$<server_instance>, NT Service\MSOLAP$<server_instance>, NT Service\MSSQL$<server_instance>, etc
* I kept the default Instance Directory: C:\Program Files\Microsoft SQL Server (and Program Files (x86)\...)
* For the instance configuration, I changed all the data and log directories to be rooted in C:\MSSQL: C:\MSSQL\MSSQL12.<server_instance>\MSSQL\Data, etc.
* I also put the Distributed Replay Client Service directories under C:\MSSQL: C:\MSSQL\DReplayClient\WorkingDir\ and C:\MSSQL\DReplayClient\ResultDir\. TBH, I'm not sure what this service is, and don't think I'll be using it. I'm not sure this was the best location or not.
* In general, I wanted the software to be under C:\Program Files, and all data to be under C:\MSSQL.
* My locale is English (Australia)
Problems:
1) The install of Analytic Services failed, but the installation otherwise was OK. The error message was:
The service cannot be started: Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. The following system error occurred: A required privilege is not held by the client.
I Googled, but none of those answers worked. The error message also seems to be misleading - what's all the blather about locale when it's a permissions issue?
So I uninstalled and reinstalled:
* I created local accounts for all services: sql_engine_service, sql_analysis_service, sql_reporting_service, etc.
* I added those users to the administrators group
* I changed my locale to English (United States)
* Installed using the local accounts instead of the default accounts.
That install worked without error. I then:
* Changed locale back to English (Australia)
* Removed all those accounts from the Administrators group
* Rebooted
All services started except Analysis Services.
I added the sql_analysis_service with Full Control permissions to C:\MSSQL\MSAS12.SQLSERVER2014 and all subdirectories.
Problems:
1) That didn't work. The only way I can get Analysis Services to run is if it's in the Administrators group.
2) There are two local groups added:
SQLServer2005SQLBrowserUser$<machinename> - Is this group used for anything? Why is is labelled SQLServer2005???
SQLServerMSASUser$<machinename>$<server_instance> - I added the sql_analysis_service user to this group, didn't make a difference, it still has to be in the Administrators group for the service to start.
3) In SSMS, I cannot connect to these services:
Integration Services - <browse for more>, Local Services does not return any instances. Manually inputting ., localhost, localhost/<server instance>, etc gives:

Is there further configuration I need to do?
4) I get this error from the Report Manager URL (http://<machinename>/Reports_SQLSERVER2014/Pages/Folder.aspx, port 80)
The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.
I could understand this on the port 443 URL, but not the port 80 URL. Is there a way to configure this to not require SSL, or otherwise fix this error?
5) Windows Firewall is active. I've added rules for both Inbound and Outbound, TCP, ports 2383, 3882, based on error messages, not sure if this made any difference?
Thanks...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Likely one of the biggest problems is that after the unintstall and reinstall routine, you were doing all types of things with the permissions, the accounts, adding the accounts to the administrators group and then installing, etc. You don't. You'll break a lot of things. The best thing to do is let SQL Server setup and manage the permissions - just chose what account the services are running under and then leave it alone and let the installation process configure everything including the account permissions. Any changes after all of that need to be done using SQL Server configuration manager and Reporting Services configuration manager.
Within the server (or computer) itself, there is a mix of permissions granted to the account you setup for the service, the service, and the Windows group. Groups are named along the lines of: SQLServer<Different groups - ReportServerUsers, MSAUsers, AgentUsers, etc>$computername$MSSQLSERVER (or instance name)
And yes, the group are all used. Or normally are when you let SQL Server manage it.
You really need to go through the documentation for how some of this works:
Configure Windows Service Accounts and Permissions

1. The permissions for the different accounts is listed in the above article. I don't know what you did with the accounts, permissions, etc for Analysis Services but messing around with it is what likely caused the problems. None of the accounts or groups needs to be in the administrators group and should not have been there when you reinstalled.
2. I don't know why the group for the browser service is named SQLServer2005SQLBrowserUser$TP420 other than that's the version when SQL Browser service was introduced. But it is used for SQL Server Browser.
Analysis services account does not need to be in the administrators group. Not sure what you were doing but if you let the install itself set this up, the appropriate account will be in the group for analysis services.
3. Make sure the services are running and download the latest version of SQL Server Management Studio and use that. Download found here:
Download SQL Server Management Studio (SSMS)
4. You should use Reporting Services Configuration Manager for all configurations. I would guess you did something with binding SSL to URL and didn't mean to. Or you meant to and didn't follow the requirements. Follow the documentation here:
Install Reporting Services native mode report server
5. You would want to do the firewall ports based on the documentation. Documentation here:
Configure the Windows Firewall to Allow SQL Server Access

Sue

Scott In Sydney Hall of Fame Points: 3331 More actions · Answer 2

Hi @Sue_H,

I had to reinstall SQL Server 2014 Developer Edition yesterday, and think I've found the issue.

I think there is a GPO that controls "Logon as a service". After the reinstall, I checked the Logon as a service right, and saw the virtual SQL Server users in that user rights role. After reboot, that role was empty and SQL Server wouldn't start.

While I'm an administrator on my desktop, the GPO trumps those rights (we need a new verb to replace "trumps"...but I digress). So, while I can create local users, I can't add them to that role. Therefore, I've changed all the services listed in SQL Server 2014 Configuration Manager to use NT AUTHORITY\LocalService.

This machine isn't externally accessible, I'm doubtful internal staff will connect to my machine, and I haven't changed any firewall rules on my machine. Given that scenario, if there is still a security concern, please let me know.

When I installed SQL Server 2014 Developer Edition on my personal laptop, it installed fine per your comments in your previous post.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

As long as you changed them through configuration manager, it should be okay.
Even without the reboot, the local policies get updated and overridden by GPO, I think it's every 30 mins. So if they are making those changes, which isn't unusual in a lot of companies, you will need to just use the system accounts which aren't as bad security wise as they used to be with the security changes they've made to SQL Server since earlier versions.

Sue