2015-09-22

Access SQL Server Configuration Manger of remote instances via Microsoft Management Console


You can connect to SQL Server Configuration Manager via Microsoft Management Console (MMC) to remotely manage SQL Server instances, which is especially useful when dealing with the instances deployed on Server Core. Before you connect it, you must deploy inbound rules to the Windows Firewall in the domain controller server, and then force member servers to apply the updated GPO. If the client is a script or a MMC snap-in, the sink is often Unsecapp.exe. Without deploying proper Windows Firewall rules, you might receive error message below when remotely connecting SQL Server Services in Computer Management:
There is no item in this view.
The RPC server is unavailable (0x800706BA).

To configure the specific Windows Firewall rule, please refer to the following steps:
1. Right-click Inbound Rules node in the target GPO, and click New Rule.
2. In the Rule Type step, choose Predefined, and choose Windows Management Instrumentation (WMI).
3. In Predefined Rules step, choose Windows Management Instrumentation (ASync-In).

For example, you are trying to remotely managing SQL Server Services of SQL-B via Computer Management in SQL-A; however, you encounter the issue described above, before you change any security settings in SQL-B, there are some basic ways could help you troubleshoot it:
1. Turn off Windows Firewall in SQL-B.
2. Turn off Windows Firewall in SQL-A.
3. Open Resource Monitor in SQL-B, switch to Network tab, open Computer Management in SQL-A and connect to SQL-B to check what processes, connections and ports are being using.
4. Open Resource Monitor in SQL-A, switch to Network tab, open Computer Management in SQL-A and connect to SQL-B to check what processes, connections and ports are being using.

Unable to connect/restart SQL Server service when deploying Log on as a service policy on SQL Server


Managed Service Account (MSA) is a special kind of domain account managed by a domain controller and is assigned to a single member computer and used for running services. The MSA password is managed by the domain controller. MSAs can register a Service Principle Name (SPN) with Active Directory. MSAs use a $ name suffix; for example, CONTOSO\SQL-A-MSA$.

If you use an MSA as the SQL Server service account, you need to grant the service account the Log on as a service right within the Group Policy Object (GPO) in the Group Policy Management. After configured the Log on as a service right, if you execute command (gpupdate /force) to force server to apply the updated GPO, you might receive error message – “The service did not start due to a logon failure” when connecting to SQL Server service, or “The request failed or the services did not respond in a timely fashion” when restarting SQL Server service. Check System event log in Windows Logs, you will find more details:
Logon failure: the user has not been granted the requested logon type at this computer.
This service account does not have the required user right Log on as a service.

The issue happens on the server instances starting SQL Server service with default virtual account. If you check Local Group Policy, you will find that NT SERVICE\ALL SERVICES is already added into Log on as a service. You can change Startup type of those SQL Server services to Automatic (Delayed Start) to minimize the conflict issue caused by Group Policy, or manually run the following steps as an alternative solution:
1. Open Services console.
2. Double-click the specific server instance you are trying to start.
3. In Properties dialog box, switch to Log On tab.
4. Clear value in the Password field, click Apply, click OK when prompting message “Passwords mismatch”, then click OK.
5. Retry starting the service.

Note that you must execute the steps above via Services console as it doesn't work in SQL Server Configuration Manager.

To execute the steps in Windows Server Core environment, the easiest way is to use Microsoft Management Console(MMC) because it can be used to access SQL Server Configuration Manager of remote instances:
1. Log on to the server that can connects to Server Core server.
2. Start an MMC snap-in such as Computer Management under Administrative Tools.
3. In the left pane, right-click the top of the tree and click Connect to another computer.
4. In Another computer field, type the computer name of the server that is in Server Core mode and click OK.

If you encounter error messages when execute above steps, please refer to Access SQL Server Configuration Manger of remote instances via Microsoft Management Console.

2015-09-18

SQL Server - Check orphaned users in databases

An orphaned user is a database user whose corresponding SQL login has been dropped or the database is restored or attached to a different instance of SQL Server. You can detect orphaned users in a database by using the sp_change_users_login stored procedure with the @Action='Report' option.  If Action parameter is specified Report, it lists the users and corresponding security identifiers (SID) in the current database that are not linked to any login.
EXEC sys.sp_change_users_login @Action='Report';
You can use the sp_change_users_login stored procedure to relink a database user with a SQL login. To link the specified user in the current database to an existing login, reference the following sample statement:
EXEC sys.sp_change_users_login
   @Action='Update_One',
   @UserNamePattern='sql_user_b',
   @LoginName='sql_user_b'