Tag Archives: security

Log reader fails with “The process could not execute ‘sp_replcmds’ ” error

Recently I worked on an issue in which the Replication wasn’t working right after setting it up. The Publication and the subscriptions were created successfully but the subscription was still uninitialized.

As a first step checked the status of the Snapshot Agent under Replication Monitor. It was failing with the following error message.

The concurrent snapshot for publication 'Sitecore_301_Redirect' is not available because it has not been fully generated or the Log Reader Agent is not running to activate it.

The Snapshot agent was correct that the snapshot was not generated and also the Log Reader agent wasn’t running. The Log Reader agent was failing with the error below.

The process could not execute 'sp_replcmds' on {ServerName}

The above message a very generic in nature. Hence I added a verbose log to the Log Reader Agent as explained in this KB article.

-Continuous -Output "D:\VerboseLog\Log.txt" -Outputverboselevel 3
LogReader_VerboseLog

 

Here is what was recorded in the Log Reader agent when the job failed the next time

Status: 0, code: 15517, text: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'.

sys.databases DMV reported that the current owner of the job wasn’t a DBO on the Publication Database. Hence the next logical step was to make the job owner the DBO of the database by executing

sp_changedbowner '{LoginName}'

After this change, all the replication agents worked without any problems and data started flowing to the subscriber.

Restirct access to Local System Administrators on SQL Server

One of my long time friends reached out to me last week regarding securing his SQL Server instance. He has a SQL Server 2005 instance hosted on a server in which many users are part of Local Administrators Group. In the recent past, he was finding it difficult to manage the SQL Server instance, since all those Local Users had sysadmin privileges on it. Since my friend was an Accidental DBA, he wanted me to suggest an option so that the sysadmin privileges on that system is only available to the sa login. Instead of sending the document over email, I thought of posting it here.

In most of the non-production instances, the SQL Server service is configured to start using the Local System account. This account gets access to the SQL Server instance through the BUILTINAdministrators group. Whoever is part of the Administrators group on the Server is part of this group. In SQL Server 2000 & SQL Server 2005, by default the BUILTINAdministrators group is added to the sysadmin server role on the SQL Server instance. To restrict access to the Local System Administrators on the SQL Server Instance, the following action needs to be performed.

  • The service account of SQL Server/Agent service needs to be granted requisite permissions on the SQL Server instance.
  • If the SQL Server/Agent service is starting using Local System account, it needs to be changed to start using a DomainWindows account.
  • The sysadmin privileges for the BUILTINAdministrators group needs to be revoked

Create a New Login in SQL Server for the Service Account

A new login needs to be created in SQL Server for the service account. This account needs to have the privileges as explained in this article. Since I know that the server where this will implemented is not so critical, it is safe to add the SQL Server service account to the Administrators group on the Server. The same account can be used to start the SQL Server Agent service or a new login with sysadmin privileges on the instance can also be created.

This login can also be created by executing the below command.

USE [master]
GO
CREATE LOGIN [DOMAINAccountName] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = 'DomainAccountName', @rolename = 'sysadmin'
GO

Change the Service Account for the SQL Server service

Once the login for the Service Account has been created on the SQL Server instance, we can go ahead and change the SQL Server service to start with the new account.

Open SQL Server Configuration Manager and change the SQL Server & Agent service account to a DomainWindows account as in this screenshot.

Similarly change the SQL Server Agent to start with the new account. After making the changes, the SQL Server and Agent services needs to be restarted.

Revoking sysadmin privileges for BUILTINAdministrators login

Before proceeding further, make sure that the sa login is enabled and you know the sa password. In SSMS open the Properties of BUILTINAdministrators login and uncheck the sysadmin server role.

Or execute the below script in a new query window.

EXEC master..sp_dropsrvrolemember
@loginame = N'BUILTINAdministrators',
@rolename = N'sysadmin'
GO

Enabling sa access

Since the requirement is to enable sa login to access the instance, SQL Server must be configured for SQL Server and Windows Authentication mode authentication. This can be configured in the SQL Server Properties screen.

After making this change, the SQL Server service needs to be restarted for the changes to take effect. Now the SQL Server instance is configured to accept SQL Server authenticated logins.

From now on none of the system administrators on the server would be able to logon to SQL Server and sa is the only login who will have access to the instance.

Drive not accessible through the Administrative Share

On the server which was rebuilt a couple of weeks ago all the Maintenance Jobs started failing with the error message that the path on which the scripts were located could not be accessed. Since the jobs are run from a third party Job Scheduling Agent, the job parameters were configured to use the script from the network share in format \servernamedrive$scriptfilename.

We were not able to access this path from the Windows Explorer as well. This was the error message.

Any share that has the $ symbol at the end of it, is called an Administrative Share. These shares are hidden in Windows Explorer. This share is accessible to only the users who are members of Local Administrators group on the Windows Server which hosts this share. The permissions for this share cannot be modified.

In this case, the account starting the job and the DBA team were part of Local Administrators group still they were getting the error related to permissions. The reason for this error could have been that the E$ share was removed by someone. But the drive had that share.

The other drives on this server were accessible through the Administrative share except for this drive. Hence it was confirmed that something was wrong with the configuration of this share. Clicking on the Share Name drop down lead to the resolution of this issue.

Someone had shared this drive with the name E. The permissions for the share were set as Read for Everyone.

Out of the two shares which had the same name the permissions for the E share which were the least, were taking precedence. Hence even an Administrator on the server was denied access to the Administrative share.

This issue was intimated to the Windows Administrators who deleted the E share. As expected the E$ was accessible to the Administrators now and all the jobs completed successfully.

From where is that Login getting its permissions?

Creating and manging logins is one of the common tasks that a DBA does. Usually the end user comes up with a request that I need to have access to that database and I need to have the same privileges as Mr. X has. But Mr.X’s login was not explicitly granted access to the database. Instead his Domain account was added to a group on the Active Directory and that group was granted requisite permissions on the instance.

Mr. X might be member of several groups on the Active Directory and out of those only few groups may have access to the databases on the instance. To create a login which has same privileges as that of Mr.X, DBA must first check to which domains groups Mr.X belongs to and out of those which groups have access to the databases. Manually getting this information from the Active Directory and then comparing it with the SQL Server Logins can be time consuming. As in the below example, the Domain Account is part of more than 50 groups.

There are many ways of getting this information using Powershell, VBScript etc. The one that I always use is the xp_logininfo extended stored procedure. The following script will let me know how the login PRAADIGAPRAADIGA is granted access to the SQL Server instance.

XP_LOGININFO 'PRAADIGAPRAADIGA'

Even though praadiga is not explicitly added as a login on the Instance, it is getting the privileges from the BUILTINAdministrators group membership.

A login can also be part of multiple groups which have access to different databases. One group may have read-only access to one database and another may have dbo privileges on another database. In this case the ‘ALL‘ parameter of xp_logininfo is very useful.

XP_LOGININFO 'loginname', 'ALL'

In this example the login has access to many databases through different Active Directory Groups.

xp_logininfo also has another useful parameter called members. This parameter can be used to get a list of all the members of a LocalDomain group and the level of access those members have on the Instance.

XP_LOGININFO 'BUILTINADMINISTRATORS', 'MEMBERS'

This stored procedure had helped me save time while responding to the Auditor’s questions. You may also find this useful.

SQL Agent job fails | SELECT permission deined on object ‘sysdbmaintplans’

Today’s post is also about SQL Server Agent and jobs not starting up. Again it is on the same instance where SQL Server Agent was not starting and yesterday I had posted on how it was fixed. SQL Server Agent was running fine and all the scheduled jobs were executing successfully except for the Backup Jobs. These jobs were taking the database backups through the Maintenance Plans (sqlmaint.exe). The job history had only the following message.

Executed as user: domain\AgentAccount. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

To get more information related to the Job failure, I enabled Verbose Logging for the SQL Agent job step is shown in the below screenshot.

As expected the Output file of the Job Step had much more details in it.

Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.2039
Copyright (C) Microsoft Corporation, 1995 - 1998
(null)
Logged on to SQL Server 'InstanceName'
as 'Domain\SQLServerServiceAccount' (trusted)
SELECT permission deined on object 'sysdbmaintplans', database 'msdb', owner 'dbo'
Process Exit Code: 1(Failed) (SQLSTATE 01000) (Message 0) slssqlmain failed (SLSTATE HY000) (Error 500000). The step failed.

Permission denied error again? The SQL Server Agent account had sysadmin privileges and some of the other jobs were successful. The catch was this line in the output file.

Logged on to SQL Server 'InstanceName'
as 'Domain\SQLServerServiceAccount' (trusted)

On this instance the Service Accounts for SQL Server and SQL Server Agent services are different. Even though the job owner was SQL Server Agent service account, the job would connect to the Instance using the SQL Server service account credentials. This is because the SQL Server Maintenance Plans make use of sqlmaint.exe which in turn calls xp_sqlmaint an Extended stored procedure. With this understanding it was easy to track down why the Maintenance Jobs were failing. The SQL Server service account was entered as serviceaccount@domain.com.

Since this server had known issues with this naming convention, all it needed was to enter the service account of SQL Server service to domain\serviceaccount.

When the SQL Server Agent service account was changed to follow the correct naming convention, SQL Server had created a new login which had access to SQL Server through Group Membership.

But the SQL Server service account did not have an individual login through Group Membership. Hence even though the Domain Group of which the service account was a member of had access to the instance, it was not getting the requisite permissions because it was unable to authenticate with the Active Directory. It is all because the way the domain and the trust across the domain had been setup.

The Change Control to modify the naming convention of the SQL Server service account was successfully implemented on Sunday and the Maintenance Jobs have run successfully ever since.

Server local connection provider has stopped listening on [ \\.\pipe\SQLLocal\MSSQLSERVER ]

On Tuesday our Monitoring Tool sent out this notification about a SQL Server 2005 instance.

Error: 26050, Severity: 17, State: 1.
Server local connection provider has stopped listening on
[ \\.\pipe\SQLLocal\MSSQLSERVER ] due to a failure.
Error: 0xe8, state: 4.
The server will automatically attempt to re-establish listening.

The instance was running fine but this error message was logged in the SQL Server Error log. Even Named Pipes protocol was enabled since I was able to connect to the instance using Named Pipes. Like SQL Server mentioned in the above error message, it automatically re-established and was listening on Named Pipes within a few seconds.

Server local connection provider has successfully re-established listening on
[ \\.\pipe\SQLLocal\MSSQLSERVER ]

Why did this error occur?

When a connection is attempted using Named Pipes a pipe is created by SQL Server and it is then enabled to accept the client connections. The error code 0xe8 means The pipe is being closed. In this case the client attempted to connect using the Named Pipes. Hence a Pipe was created. But the client closed the connection before the pipe was ready for accepting new client connections. Hence SQL Server stopped listening on Named Pipes.

This error was a one time occurrence in this case. If this message appears in the SQL Server Error Log often, then a SQL Server Trace can be used to find out which application is causing these errors.

The SQL Server service did not start due to a logon failure

Monday night my team called me up to inform that were having some issues while trying to start SQL Services on an instance. The services did not start automatically after the server was rebooted. When they tried starting the services manually, the following error message was thrown.

The error message is usually associated with a wrong password for the Startup Account. But as far as I knew no Change Controls were implemented on this instance and the password of this account was never changed. Quickly looked up in Active Directory Users and Computers to check if this account was locked out. No, it was not locked out.

Since it is not new to hear “someone” or “something changed” stories on the servers, I decided to re-enter the password in the SQL Server service properties. After punching in the password, the following message popped up.

Strange! The same account was used to start the SQL Server service for a very long time, now it got the Log On As a Service right? Something must be wrong. On this server SQL Server and SQL Server Agent have different startup accounts. Before re-entering the password for the SQL Server Agent service, I decided to check the settings of Log on as a Service in Local Security Settings on the server.

I could see the SQL Server service’s startup account listed here (since I punched in the password again) but the one for SQL Server Agent service was missing! This made it clear why the services were failing with logon failure error. The Service Accounts did not have Log on as a Service rights! For any service to start, it should have this right. How did this got changed? A quick look in the Change Management application revealed that a series of Windows Security hot fixes were applied some time back. One of these hot fixes would have wiped out the permissions for the SQL Server startup accounts.

Since the root cause has been figured out, I happily entered the password for the SQL Server Agent service. Same informational message that it has been granted the rights. The services started and Pradeep closed the lid of the laptop to catch some sleep.