Category Archives: Configure

Jobs fail with “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” error

Recently I worked on an issue in which the jobs which were running fine started failing all of the sudden. The job history had the following error message.

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

But the principal “dbo” exists, it can be impersonated and I have permission because I am the sysadmin! Now what changed?

Checked the job properties and “sa” was the job owner. This job was last modified a few years ago. The next step is to find out the properties of the database against which the job is executing the queries.

When I right clicked on the database and selected properties, got the following error.

Property Owner is not available for Database ‘[DatabaseName]‘. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

This error is generated when the database owner’s login does not exist in the active directory and I had blogged about it here.

When I executed sp_helpdb, the output looked like the one below.

sp_helpdb_invalidowner

As evident from the output, the database owner’s login was not present in the Active Directory and it was reported as ~~unknown~~. Now that we know the problem, the resolution is pretty simple. Just executed sp_changedbowner or ALTER AUTHORIZATION command against the databases in question. The job started running successfully.

The next time you create a database please take care to change the database owner to a different login. Else you leave the organization and someone else needs to cleanup your name from all the databases that you had created!

Configuring SQL Server to use Windows Large-Page allocations

One of the Change Controls implemented today had inspired me to write this post. The Change Control was to enable Trace flag 834 on a SQL Server 2005 instance running on Windows 2008 Server (64 bit). As explained in this article, after enabling TF 834, SQL Server will make use of Large-Page allocations for the Buffer Pool memory. Here is my understanding of Large-Page allocations and its performance benefits.

Page allocations

The Virtual Address Space is made up of a number of pages. These pages are of two sizes. Large and Small. The Small pages are 4 KB in size (8 KB on IA64 systems) and the Large pages are 2 MB in size for x64 systems (16 MB for IA64 systems). The details regarding Virtual Addresses is maintained in a structure called Page Tables. Hence each Virtual Address has a corresponding Page Table Entry in the Page Table. The hardware translates these entries into a format readable by it. Whenever an address translation occurs, the entries in the Page tables has to searched. In order to speed up the lookup process, CPU maintains a cache called Translation Look-Aside Buffer (TLB). TLB works similar to the procedure cache, such that an entry once translated need not be translated again.

With this information it will be easy to understand how Large and Small page allocations make a difference to the performance. When a Virtual Address is making use of Small pages, the number of entries in the Page Table increases, which in turn increases the number of entries in the TLB. For example for few Virtual Addresses to be cached, using Small pages it would require more entries in TLB. More entries means whenever a new translation request is received, more number of cached entries in the TLB needs to be recycled. Hence allocating Virtual Addresses by means of Large pages has a definite performance benefit.

Page allocations and SQL Server

Like I mentioned earlier using Trace Flag 834 would force SQL Server to make use of Large Pages for Buffer Pool. This trace flag is only applicable to 64-bit of SQL Server and this also requires Lock Pages in Memory privilege for the SQL Server service account.

When SQL Server is making use of Large Page allocations, entries similar to the one below are logged in the SQL Server Error Log when the service starts.

Large Page Extensions enabled.
Large Page Granularity: 2097152
Large Page Allocated: 32MB
Using large pages for buffer pool.
10208 MB of large page memory allocated.

If the SQL Server service account does not have the Lock Pages in Memory privilege, this error message is logged.

Cannot use Large Page Extensions: lock memory privilege was not granted.

This article very nicely explains the Large Page allocations and also the fact that SQL Server Enterprise Edition (64-bit) will make use of Large Page allocations without the Trace Flag 834.

This is an attempt by me to cover a very vast topic with whatever I know about it. Hope you’ll like it.

 

Disable Unsigned driver installation warning in Windows

Last year when we were applying SQL Server service packs across enterprise, on few of the instances during the Service Pack installation the following warning message was displayed.

The software you are about to install does not contain a Microsoft digital signature. Therefore, there is no guarantee that this software works correctly with Windows.

After clicking the Continue anyway button, this warning would disappear. But the Service Pack installation used to fail. This is because the Driver Signing Options are by default set to Warn - Prompt me each time to choose an action in Windows. To overcome this issue, the Driver Signing Options need to be set to Ignore. This option can be configured as seen below.

Windows Server 2003

Open System Properties and select Driver Signing.

In the Options screen select Ignore.

This change will come into force without rebooting the server.

Windows Server 2008

Open the Command Prompt as Administrator.

In the Command Prompt enter the following command.

BCDEdit /Set LoadOptions DDISABLE_INTEGRITY_CHECKS

Using Local Group Policy Editor

This option can also be set using the Local Group Policy Editor and it is applicable to both Windows Server 2003 and 2008. Launch the Local Group Policy Editor by executing   Run –> gpedit.msc. Then select Code signing for device drivers as seen in the below screenshot.

In the Properties screen, select Ignore.

Using the methods explained above, the Unsigned driver installation warning can be disabled. After making this change, the SQL Server service pack completed successfully. Then the Driver Signing Options were brought back to the original state.

FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.)

While I was testing out the method to remove trace related messages from the error log yesterday, I stumbled upon this error message in my Virtual Machine.

Error: 17207, Severity: 16, State: 1.
FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file 'C:\AdventureWorks_Data.mdf'.

Thinking that it was a one time occurance, I restarted the SQL Server service. Still the error message persisted. But there was no issues with the AdventureWorks database. It was online. I found out some useful information regarding these error messages in this KB article.

As per the information available in that article, SQL Server makes use of alternate streams while creating the internal snapshots. Starting SQL Server 2005, when DBCC CHECKDB executed against a database, an internal snapshot of the database is created and the actual DBCC is executed against the snapshot. (Here I had talked about the CHECKDB failing due to lack of space for the snapshot to be created). When the CHECKDB command completes, the database snapshot is dropped. At times, this snapshot does not get dropped. In such situations, SQL Server will try to clean up these internal snapshots when the SQL Server service is started again. At that time if these file streams are accessed by some other application, SQL Server will not be able to clean them up. Hence the above error messages are logged in the Error Log.

I remembered running DBCC CHECKDB against AdventureWorks database sometime back. It is likely that I would have abruptly shutdown my VM and the internal snapshot did not get cleaned up. During SQL Server startup, some application was accessing it. The first culprit that came to my mind is the Antivirus software.  A quick check revealed that the database files were also being scanned by the antivirus application. I configured the Antivirus application to exlcude the database related files as seen in this screenshot.

After excluding the database related file extensions in the Antivirus application, I restarted the SQL Server service. Its gone! The The handle is invalid error message disappeared from the SQL Server Error Log. It is always recommended to exclude the database file extensions/folders hosting the database files, in the Antivirus software. This post adds one more reason why it should be.

Remove SQL Server Trace messages from Error Log

It is common practice for a DBA to run Profiler/Server-side traces in order to troubleshoot performance issues. At times some server-side traces are also run for Auditing purposes. Whenever a SQL Server Trace is started or stopped, the following messages are logged in the SQL Server Error Log.

SQL Trace ID 2 was started by login "loginname"
SQL Trace stopped. Trace ID = '2'. Login Name = 'loginname'.

This is an informational message. But if the traces are started and stopped frequently, these messages can really bloat the SQL Server Error Log. I have seen a couple of instances in my environment which have these messages every 5 minutes. The relevant information can be easily searched in the SQL Server Error Log as I had explained here. But if these messages are causing the SQL Server Error Log to grow and thereby consuming a lot of drive space, then the DBA has to think of some alternative.

The easiest alternative is to stop these informational messages from getting logged in the SQL Server Error Log. Fortunately SQL Server 2005 onwards, the trace flag 3688 has been included. Starting the SQL Server with this trace flag on, the trace related messages do not get logged in the Error Log. Here is how to add this trace flag in SQL Server Configuration Manager.

After adding this trace flag, the SQL Server service needs to be restarted for the changes to take effect. This trace flag will stop all trace related messages (including the default trace) from appearing in the SQL Server Error Log.

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 BUILTIN\Administrators 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 BUILTIN\Administrators 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 Domain\Windows account.
  • The sysadmin privileges for the BUILTIN\Administrators 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 [DOMAIN\AccountName] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = 'Domain\AccountName', @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 Domain\Windows 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 BUILTIN\Administrators login

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

Or execute the below script in a new query window.

EXEC master..sp_dropsrvrolemember
@loginame = N'BUILTIN\Administrators',
@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.

Cannot open user default database. Login failed

An hour ago my team was trying to figure out why few maintenance plans on one of the Production Instances is failing. The job was failing with the below error message.

The Execute method on the task returned error code 0x80131904 
(Cannot open user default database. Login failed.  
Login failed for user '****\SQLAgt'.)

From the error message it is obvious that the owner of the job SQLAgt does not have access to its default database. Out of curiosity, I logged onto the instance and issued sp_helplogins command for that login (it is a group of which SQLAgt is a member).

Opened the login properties in SSMS.

The Default database is blank. For sure that database does not exist or is offline. A quick sp_helpdb confirmed that the database does not exist. Then I recollected that some time back team had implemented a Change Control to drop one of the databases on this instance. But while implementing it they did not take this consideration. As a result any logins which had this dropped database as the Default database will not be able to login to the instance. Executing this query will change the default database to master for that login.

Exec sp_defaultdb @loginame='DomainName\SQLAgt', @defdb='master'

I use the following scripts to look for any logins that have the to-be dropped database as the Default Database, before dropping the database.

SELECT name FROM syslogins WHERE dbname='DatabaseName'
SELECT name as [LoginName]
FROM sys.server_principals 
WHERE is_disabled = 0 
AND default_database_name='DatabaseName'

This situation could have been easily avoided, had the DBA checked for the logins that have this database as default, before dropping the database. Now one more Change Control, approvals and so on for no reason.