The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

Posts Tagged ‘SQL’

Checklist for SQL Server Database Audit & Security Hardening

Posted by Sudarshan Narasimhan on June 8, 2016


TheAuditIsComing
I’ve been involved in a lot of IT security audits, many times due to legislative requirements. A few good examples of these are PCI, SOX, HIPAA, GLBA audits. Each of these US legislative acts have a set of defined security standards & configurations that organizations have to adhere to.  Audits for these systems happen both internally and externally to ensure the company is compliant to these standards and any remediation’s are acted upon.

Being a DBA, you’re usually involved in these IT audits. The scope for these audits should be very well defined per your internal IT security policies. As a database administrator, you are responsible for the secure management of the company’s data, vis-à-vis compliance data. The systems in scope for these databases are usually ones (but not limited to) that contain the following data:

  1. Payment Card Data or database systems used for Payment processing (Online payment web systems, retail POS etc)
  2. Financial Data (like Bank, Insurance, Stocks etc)
  3. Health Information (like medical, patient records etc)
  4. Personally Identifiable Information (PII) data (like client/member data including DOB, Tax Number, SSN etc)

While each of these compliance regulations are very-well documented (especially PCI DSS), you must follow certain best practices or standards for any DBMS system, irrespective of the requirements. In today’s world, data leaks are becoming increasing prevalent and within the DB world, it is very essential to adhere to strict & enforceable data security practices.

All that said, coming to SQL Server security good practices, I’ve listed what I feel are the bare-minimum security checks that need to be performed. You can add stuff to this, but in my view these are essential and non-negotiable settings that you must check as a DBA. I’ll probably share some scripts & code to monitor these setting centrally in an automated way, in another post. This post is to list down and share these settings so that you are aware of the various things to consider when looking at SQL Server Security Hardening & Audits.

 

CHECKLIST: Security Audit of a SQL Server Database Instance

 

Instance Level:-
1. Server Role Membership
2. Server Permissions e.g. CONTROL SERVER, ALTER ANY DATABASE
3. Public Role Permissions (harden this as much as possible on user database, leaving aside system databases)
4. BUILTIN Admins present as a login?
5. Default Port changed?
6. Orphaned AD Users & Groups must be removed
7. [NT AUTHORITY\ANONYMOUS LOGON] must be removed.

Database Level:-
1. Database Role Membership (Elevated)
2. Database Role Membership (Standard)
3. Database Permissions e.g. DELETE, ALTER, SCHEMA
4. Guest User Disabled?
5. DBO User ownership (you can read my previous post on DBO ownership HERE (point #2) to understand why this is important)
6. No Orphaned Users should exist

Windows OS Level:-
1. Local Administrators Group Members
2. Local Security Policy Privileges (following this MSDN  doc to grant only those privileges required to run SQL Services to the SQL service accounts)
3. Guest User enabled?
4. Windows Firewall configured to allow connections via a specific Port Only (I hate systems setup to allow all incoming connections to sqlservr.exe. Duh!)
5. SQL Browser Service Disabled?

Configuration:-
1. XP_CMDSHELL Disabled?
2. Password Complexity & Lockout for SQL Logins (SQL logins inherit the account & password policies of the server on which the instance is running. Work with your IT admins to implement a stronger policy using GPO if required. Otherwise make sure CHECK_POLICY setting is turned ON for all SQL logins [including SA])
3. SA password rotated frequently (if you can disable the SA account, even better)
4. Cross database ownership chaining Disabled?

Patch Management
1. Are all the security bulletin patches for known vulnerabilities applied on your SQL Server instance. (Refer to https://technet.microsoft.com/en-us/security/bulletins.aspx and filter on your specific version & patch level to get the list of known security patches available).
2. Are all known Windows security patches applied (Depending on your company, this might be handled by another team, but it’s still a DB Server, so better be prepared if someone else isn’t doing their job properly 😉 )

Compliance:-
1. Access Control mechanisms in place for Sensitive/PII Data?
2. Access Control mechanisms in place for Regulatory Data?
3. SQL Server Audit enabled to track permissions/role/users changes to compliance data?

Penetration Tests
1. SA Login checks. Is anyone trying to login by brute-force using the SA account?
2. Main App login. Is anyone trying to login by brute-force using the application login (especially if it’s a SQL login)

 

I was almost about to add managed service accounts (MSA/GMSA) to this list, but considering SQL 2016 just released a week ago and most of you are probably running SQL 2012/2014 with either Clustering/AlwaysON, I’m not going to make it a must-do yet. (Damn you Microsoft, you promised GMSA support for SQL 2014 but didn’t follow through 😦 )

That’s all for now. I’ll continue this in another post with some sample code to help you through all these items.

Until then, stay safe and may the force be with you.

-TheSQLDude (Sudarshan)

Advertisements

Posted in Auditing, General Thoughts, Security | Tagged: , , , , , , | Leave a Comment »

Quick SQL Server Info – Script

Posted by Sudarshan Narasimhan on April 19, 2016


Here is a handy script that I used to quickly get high-level information about any SQL Server Instance. It provides basic information like machine name, number of CPU’s, memory, patch level, HA configuration and the user databases hosted on this instance.

/*SCRIPT START*/
DECLARE @cpu int, @memory decimal(2), @dbcount int, @dbs varchar(2000)
SELECT @cpu=cpu_count FROM sys.dm_os_sys_info
SELECT @memory= CONVERT(decimal(2),ROUND([total_physical_memory_kb]/1024.0/1024.0,1)) FROM [sys].[dm_os_sys_memory]
SELECT @dbcount=COUNT(*) FROM sys.databases where database_id>4
SELECT @dbs = (SELECT STUFF(
    (
    SELECT ', ' + DB.name
    FROM sys.databases AS DB
    WHERE DB.database_id>4
    FOR XML PATH('')), 1, 2, '') AS Databases)
SELECT 
@@SERVERNAME as [SQLServerName]
,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as [MachineName]
,[CPU] = @cpu
,[Memory (GB)] = @memory
,[Num.Databases] = @dbcount
,[Version] = 
    CASE WHEN CONVERT(varchar(50), SERVERPROPERTY('ProductVersion')) LIKE '9%' THEN 'SQL 2005'
        WHEN CONVERT(varchar(50), SERVERPROPERTY('ProductVersion')) LIKE '10.0%' THEN 'SQL 2008'
        WHEN CONVERT(varchar(50), SERVERPROPERTY('ProductVersion')) LIKE '10.5%' THEN 'SQL 2008R2'
        WHEN CONVERT(varchar(50), SERVERPROPERTY('ProductVersion')) LIKE '11%' THEN 'SQL 2012'
        WHEN CONVERT(varchar(50), SERVERPROPERTY('ProductVersion')) LIKE '12%' THEN 'SQL 2014'
        WHEN CONVERT(varchar(50), SERVERPROPERTY('ProductVersion')) LIKE '14%' THEN 'SQL 2016'
        WHEN CONVERT(varchar(50), SERVERPROPERTY('ProductVersion')) LIKE '15%' THEN 'SQL vNext'
    ELSE 'UNKNOWN' END
,SERVERPROPERTY('ProductLevel') as [ServicePackLevel]
,ISNULL(SERVERPROPERTY('ProductUpdateLevel'),'N/A') as [UpdateLevel]
,ISNULL(SERVERPROPERTY('ProductUpdateReference'),'N/A') as [UpdateKBNumber]
,SERVERPROPERTY('Edition') as [Edition]
,SERVERPROPERTY('IsClustered') as [Clustered]
,SERVERPROPERTY('IsHadrEnabled') as [isAlwaysON]
,[UserDatabasesHosted] = @dbs
GO
/*SCRIPT END*/

Posted in T-SQL | Tagged: , , | Leave a Comment »

IO Cost in an execution plan–What it actually means?

Posted by Sudarshan Narasimhan on May 11, 2012


I/O is probably one the slowest of the resources. The information below gives a quick meaning of what these operators mean when you look at them in an execution plan. This specific concentrates on the “Estimated I/O Cost” value in an execution plan. I’ll post some other day in detail on how to decipher a SQL Server query execution plan.

Thanks to Niraj Mehta for putting together this content.

Table Scan

  • The total number of data pages in the table

Clustered Index Scan

  • The number of levels in the index plus the number of data pages to scan (data pages = #rows / #rows per page)

Non-Clustered Index Seek on a Heap (Bookmark Lookup)

  • The number of levels in the index plus the number of leaf pages to read for qualifying rows plus the number of qualifying rows (1 I/O for each row on the heap)

Non-Clustered Index (I1) Seek on a Clustered index (I2) (Bookmark Lookup)

  • The number of levels in the I1 index plus the number of leaf pages to read for qualifying rows plus the number of qualifying rows times the cost of searching for a clustered index (I2) key

Covering Non-Clustered index

  • The number of levels in the index plus the number of leaf index pages to read for qualifying rows (#qualifying rows / # rows per leaf page).

Posted in Performance | Tagged: , , , , , , , , , , | Leave a Comment »

SQL Server 2012 – Released and available for public download

Posted by Sudarshan Narasimhan on April 3, 2012


SQL Server 2012 codenamed “Denali” is now ready for download and available for public consumption. Please check out the following blog post that talks about the general release.

http://blogs.technet.com/b/dataplatforminsider/archive/2012/04/02/sql-server-2012-is-generally-available.aspx

SQL 2012 is specifically targeted at Big Data and High Availability, including a powerful set of feature-rich BI tools like PowerView. Some of the new engine features are :-

  1. SQL Server AlwaysON
  2. Contained Databases
  3. Column Store Indexes
  4. User-Defined Server Roles
  5. Big Data (Hadoop)


Again, Books Online is your best resource to read up on the new release and to understand the new set of features.

You can download the evaluation/trial version of SQL 2012 here,

http://www.microsoft.com/sqlserver/en/us/get-sql-server/try-it.aspx

http://www.microsoft.com/download/en/details.aspx?id=29066

Posted in SQL News | Tagged: , , , , , , , | Leave a Comment »

How To: SQL Server Bulk Insert with Constrained Delegation (Access is Denied)

Posted by Sudarshan Narasimhan on December 30, 2011


Well folks, I’ve not been able to blog for sometime as I was caught up with work. But fear not, these past 2 months have given me enough subject material for multiple blog posts. Let me start off with one regarding setting up SQL Server for bulk inserts from a network file share when constrained delegation is setup in Active Directory.

For SQL Server folks out there, I don’t expect you to know about Delegation let alone Constrained vs. Unconstrained.

Q&A

Q: What is Delegation?
A: A client connected to an instance of SQL Server can connect to another instance of SQL Server or another machine by forwarding the credentials of an authenticated Windows user.

E.g. Domain\User1 on Client1 connects to –> SQL Server SQL1 and accesses a remote file on –> Server2, using his own credentials i.e. Domain\User1

It is the role of the SQL Server machine SQL1 to impersonate/delegate that user when communicating with Server2. For this to happen, the SQL Server has to be configured to allow delegation (in Active Directory).

Q: What is Constrained Delegation?
A: This feature allows administrators to specify exactly which services a server or a domain account can access when using an impersonated user’s security context. More on this later. If there is no such restriction configured, then it is called as unconstrained delegation, where every service for that user can perform impersonation aka delegation. It is a security best practice to perform constrained delegation as it reduces the surface area of any attacks.

Environment

Here is a brief layout of the environment that I am going to use in this post as a reference.

image

This is a basic 3 machine architecture that is very common. Here are the various parties involved:-

  1. Client Computer – Windows PC/Workstation/Application Server: machine name TRINITY1, Client account Domain\appadmin
  2. SQL Server – Windows Server 2003+ running SQL Server 2005/2008/2008 R2 standalone instance: machine name NEOSQL, SQLSvc account Domain\sqladmin
  3. File Share on another Windows Server (basically a shared folder): machine name MORPHEUS1, where user Domain\appadmin has Full Control on the shared folder.

 

Scenario/Requirement

In an ideal scenario, here is how I want things to work. When a client application runs the SQL BULK INSERT command logged in as Domain\appadmin account to SQL Server, I want the same appadmin account to access the remote file share and read the data.

1. If a user uses a SQL Server login to connect to SQL, then the SQL Service account credentials are used to access the remote file share.

2. If a user uses a Windows account, then his own account is used to access the file share and for this to work successfully, delegation has to be configured.

BULK INSERT appdb.dbo.bulktbl

FROM ‘\\morpheus1\share\data.txt’

If not configured correctly then you will get this error when running the above bulk insert command.

Msg 4861, Level 16, State 1, Line 3

Cannot bulk load because the file "\\morpheus1\share\data.txt " could not be opened. Operating system error code 5(Access is denied.).

Configuring Unconstrained Delegation

1. Configuring permissions on the shared folder on Morpheus1. As you can see the appadmin windows account has Full Control and the sqladmin account has Read/Write permission (if any sql login is going to be used, this is not mandatory).

image

2. I am assuming that the SQL Server is running under the service account Domain\sqladmin. Login to the Domain Controller with Domain Admin rights and open up Active Directory Users and Computers MMC snap-in.

a. Make sure that the following is NOT checked.

image

b. The SQL Service account needs SPN’s (Service Principal Names) to be created before it can be configured for delegation. You can use the SetSPN.exe tool that is available with Windows SDK or sysinternals toolkit to create the SPN’s. This tool is also available bundled along with Windows Server 2008.

We need to create 4 SPN’s for the account running SQL Server service as shown below. (2 with netbios names and 2 with FQDN). Note: The command below is for a standalone default instance of SQL Server running on default port 1433. Please modify as applicable to your environment.

Setspn -A MSSQLSvc/neosql thematrix\sqladmin
Setspn -A MSSQLSvc/neosql:1433 thematrix\sqladmin
Setspn -A MSSQLSvc/neosql.thematrix.sudarn.com thematrix\sqladmin
Setspn -A MSSQLSvc/neosql.thematrix.sudarn.com:1433 thematrix\sqladmin

Once done you can query the SPN’s using setspn.exe and it should list you these 4 SPN’s.

image

c. Switch to the Delegation Tab and select the radio button by Trust this computer for delegation to any service (Kerberos only). We are setting up unconstrained delegation if this option is chosen. If you do not see the Delegation tab available, then there was some issue with creating the SPN’s in step (b) listed above.

image

d. Next find the actual machine account in Active Directory for the SQL Server machine NEOSQL and set the computer account to be trusted for delegation, as we did above.

image

e. Next, we need to check the file server where the file we want to import is located, i.e. MORPHEUS1. We need to verify that this machine has the normal 2 HOST SPN’s registered.

image

This is required to use Kerberos for authentication. Delegation will not work without this, and you will receive an error otherwise. By default each machine should have 2 HOST SPN’s created for it.

 

3. On the SQL Server machine NEOSQL, open up the Local Security Policy by going to secpol.msc –> Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment, and add the sql account Domain\sqladmin to the following policies:

  • Act as part of the operating system
  • Impersonate a client after authentication

So far the configuration given above is for unconstrained delegation. We need to make sure that this setup works before we can configure constrained delegation. Once you are done till there, reboot the SQL Server machine (NEOSQL) and the file share machine (Morpheus1), so that all the changes we made in Active Directory are reflected when the machine starts up.

Testing

Make a test connection from the client machine (TRINITY1) using sqlcmd or SSMS. Open up another connection from SSMS and run the following query to find out if the connection from the client machine is using Kerberos authentication.

select b.spid, b.hostname, b.program_name, a.auth_scheme

from sys.dm_exec_connections a

inner join sys.sysprocesses b

on a.session_id = b.spid

image

The connection from the client machine should return KERBEROS. This is required for delegation to work. If it did, then you are good to run the bulk insert statement and it should work.

Configuring Constrained Delegation

So far so good. Now comes the part that is the crux of this blog post. If you were successful in getting things to work, then read on….

1. Open up Active Directory Users and Computers MMC snap-in with a Domain Admin account as before. Open up the Domain\sqladmin account and switch to the Delegation Tab.

2. Choose the option “Trust this computer for delegation to specified services only”, select “Use any authentication protocol” and click on the “Add…” button.

image

In the dialog to select the sqladmin account (or your account which runs the SQL service).

image

 

This should list the 2 SPN’s we have previously created for this account. Select both of them and click OK.

image

Click on the “Add…” button again and enter the SQL Server machine name (NEOSQL) and choose the HOST service.

image

Click on the “Add…” button again and enter the File Share machine name (MORPHEUS1) and choose the HOST Service. We are actually interested only in the CIFS and Protected Storage service. Choosing HOST will automatically choose these 2 services.

 

image

 

After adding all of these, this is how your final configuration for the sqladmin account will look like.

image

We need the SQLSvc account (Domain\sqladmin)and file share server to have CIFS service enabled for delegation because we are accessing a remote file share and it is the role of the CIFS Service (Common Internet File Share) to perform this. If we do not have CIFS (which comes when we added the HOST Service), then your account (Domain\appadmin) from the client machine (TRINITY1) will reach the file share server (MORPHEUS1) as NT AUTHORITY\ANONYMOUS LOGON and this will not have access and fail with 0x5 (Access is Denied).

I tested this by enabling auditing on the shared folder and saw this. This is a easy test to perform to check if delegation is working or not

When the bulk insert fails with access denied we will see this in the security event log of the file server (MORPHEUS1),

Log Name:      Security
Source:        Microsoft-Windows-Security-Auditing
Date:          12/20/2011 11:17:33 PM
Event ID:      4624
Task Category: Logon
Level:         Information
Keywords:      Audit Success
User:          N/A
Computer:      MORPHEUS1.THEMATRIX.sudarn.com
Description:
An account was successfully logged on.

Subject:
    Security ID:        NULL SID
    Account Name:        –
    Account Domain:        –
    Logon ID:        0x0

Logon Type:            3

New Logon:
    Security ID:        ANONYMOUS LOGON
    Account Name:        ANONYMOUS LOGON
    Account Domain:        NT AUTHORITY

That’s all folks! Keep in mind that this configuration requires a good bit of handling with Active Directory and/or policies. Please work with your Domain Administrator while making these changes. As always, stay tuned for more… Cheers and a happy new 2012 to y’all!

 

Posted in Security | Tagged: , , , , , , , , , | 34 Comments »

The Dabbling Developer Series: Part1-Windows Service Management

Posted by Sudarshan Narasimhan on September 30, 2011


Now and then when I get some time I like to spend it writing code and I usually like to relate this to some work/activity that helps in my main work i.e. SQL Server. I have many small single-purpose applications/utilities written over time and I realized that having them saved on my PC is of no use. Don’t be surprised if you find C/C++ code samples posted on TheSQLDude. For the time being let me be the TheCDude and Long Live Dennis Ritchie & Ken Thompson!

 

Service Control Manager Test

I wrote some code to test if I can open the Windows Service Control Manager (SCM) for the local machine or remote machine with the current logged-in account. This helps me validate permissions or spot other errors when performing service related activities. For those of you who don’t know what SCM is read this article.

SCM is a system component that takes care of service management, service start-up and other service related activities.

You might ask, How did I come to care about SCM all of a sudden? What the hell does this have to do with SQL Server?

Let me explain. I came about SCM when a customer of mine came up with a SQL Server Setup issue. SmileWhen SQL Server setup goes about installing the various SQL Server components, one of the things it has to do is to create the SQL Server services like SQL Engine, RS, AS, Browser service, SQL Writer service etc. Obviously if setup is not able to do this, it will fail. For someone to create/delete/modify a Windows service, they need to talk to the Service Control Manager (SCM). How do you isolate whether the problem is with SQL Setup or some other component? This is how I ended writing this code to test SCManager.

If you want to perform windows service related activities, it works like this:-

1. You need to open the SCManager, which will let you access its database which has the list of registered services

2. If this succeeds, then depending on the operation you can either Open an existing service and modify/query its properties or create a new service and register it with SCM.

 

The code below is simple C code written on Visual Studio 2010. This has no copyright so feel free to re-distribute.


// scmantest.cpp : Defines the entry point for the console application.

//

 

#include "stdafx.h"

#include <windows.h>

#include <tchar.h>

#include <strsafe.h>

#include <stdio.h>

#include <conio.h>

 

#pragma comment(lib, "advapi32.lib")

#pragma comment(lib, "Kernel32.lib")

 

int _tmain(int argc, _TCHAR* argv[])

{

 if (argc < 2)

 {

  _tprintf(_T("Usage : %s name_of_machine_to_test"), argv[0]);

  return 1;

 }

 

     //CHAR lpMachine ;

    BOOL bReturn = FALSE;

    SC_HANDLE hScm;

 

 

    // Check that we can open the SCManager on the machine

    hScm = OpenSCManager(argv[1], NULL, SC_MANAGER_ALL_ACCESS);

    if (hScm)

    {

        bReturn = TRUE;

        printf("OpenSCManager completed successfully\n");        

        CloseServiceHandle(hScm);

    }

      else

      {

        _tprintf(_T("Could not OpenSCManager on %s. GLE = 0x%08x\n"),argv[1],GetLastError());

        return 1;

      }

 

 _getch();

 return 0;

}

 


The above code tries to Open the SCM and if that fails then it reports the Win32 error. You can Bing! this error to find out what action to take. The application scmantest takes 1 parameter which is the Machine Name. So if you PC is called WorkPC.domain.company.com, then you would run this from the command prompt like this

image

Here is an output for a non-existent machine and you can see that the Win32 Error reported is 0x000006ba (The RPC server is unavailable).

image

 

This can help isolate permission issues when trying to open SCM database as you need to have Administrator rights. Sometimes certain permissions to create might be missing which you will have to grant.

If you are a Windows Admin reading this then here are some command-line instructions to operate on Windows Services

1. Query the state of an existing Windows Service – sc query ServiceName

e.g. Checking state of default instance of SQL Server.
sc query MSSQLSERVER

SERVICE_NAME: MSSQLSERVER
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 4  RUNNING
                                (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x0
        WAIT_HINT          : 0x0

If you specified an invalid service name, you will get this error,
[SC] EnumQueryServicesStatus:OpenService Failed 1060:The specified service does not exist as an installed server.

2. Create a new Windows Service – sc create …

Let me take an example of creating a SQL Server service

e.g.  Creating a Default instance of SQL Server

sc create MSSQLSERVER type= own start= demand error= normal binPath= D:\MSSQL\MSSQL.1\MSSQL\Binn\sqlservr.exe obj= LocalSystem DisplayName= "SQL Server (MSSQLSERVER)"

type –> Type of service you want to create. Leave it at OWN.
start –> Start type for the service. You can have it at automatic, disabled. We choose demand which means manual start-up.
binpath –> Complete path to the sqlservr.exe
obj –> Startup account for the service.
DisplayName –> can be anything, this is what services.msc will show us.

Note:-
a) There must be a space between ‘=’ and the value.
b) Double-quotes are mandatory is there is a space in the path.

 

For those of you who didn’t know this, the windows service entries are present in registry in HKLM\SYSTEM\CurrentControlSet\Services\. This is the same location from where services.msc reads and displays the list of services.

 

3. Using SUBINACL to check permissions for Windows Services:

SubInACL is a command-line tool that is available for download here and shows you security information about files, registry keys, and services.

e.g. Query permissions on the service for default SQL Server instance

subinacl /verbose /service MSSQLSERVER /display

/owner             =system
/primary group     =system
/audit ace count   =1
/aace =everyone         SYSTEM_AUDIT_ACE_TYPE-0x2
        FAILED_ACCESS_ACE_FLAG-0x80    FAILED_ACCESS_ACE_FLAG-0x0x80
        SERVICE_ALL_ACCESS
/perm. ace count   =6
/pace =system   ACCESS_ALLOWED_ACE_TYPE-0x0
        SERVICE_QUERY_CONFIG-0x1           SERVICE_QUERY_STATUS-0x4           SERVICE_ENUMERATE_DEPEND-0x8
        SERVICE_START-0x10                 SERVICE_STOP-0x20                  SERVICE_PAUSE_CONTINUE-0x40        SERVICE_INTERROGATE-0x80
        READ_CONTROL-0x20000               SERVICE_USER_DEFINED_CONTROL-0x0100
/pace =builtin\administrators   ACCESS_ALLOWED_ACE_TYPE-0x0
        SERVICE_ALL_ACCESS

4. Granting permissions using SUBINACL

e.g. Grant Full Control to TestUser1 on the default SQL Server instance’s service.

subinacl /service MSSQLSERVER /Grant=Domain\TestUser1=F
MSSQLSERVER : new ace for anselm\administrator
MSSQLSERVER : 1 change(s)

Other useful Resources

Using SC.EXE to Develop Windows NT Services
http://msdn.microsoft.com/en-us/library/ms810435.aspx

How to create a Windows service by using Sc.exe
http://support.microsoft.com//kb/251192


I hope this post was useful for both Developers as well as Administrators. Would appreciate any feedback on this post since I am thinking of posting other useful code samples in the future. As always stay tuned for more geeky info….

 

Posted in Code Samples | Tagged: , , , , , , , , , | Leave a Comment »

PRB: Unable to remove secondary filegroup due to system service broker queues

Posted by Sudarshan Narasimhan on August 10, 2011


Delving a little into SQL Storage engine and Service Broker today. I had faced this problem sometime back and it presented me with an opportunity to see how the SSB queues/tables are represented internally by SQL Server.


Scenario
My customer wanted to remove the secondary File Group “FG2_fg" from a database. But was unable to remove the filegroup since these service broker tables were present in filegroup FG2_fg.

queue_messages_223180947
queue_messages_225181061
queue_messages_327181175

All of the above objects are internal system tables which are placeholders for the default queues that are present for every database starting with SQL Server 2005.

QueryNotificationErrorsQueue
EventNotificationErrorsQueue
ServiceBrokerQueue

Whenever we tried to remove the file/filegroup we got this error,

Alter database test3 remove file fil2

Msg 5031, Level 16, State 1, Line 1

Cannot remove the file ‘fil2’ because it is the only file in the DEFAULT filegroup.

We used the following queries to identify the objects present in the secondary filegroup:-

1) Find out the objects present in the secondary filegroup

select name, type, type_desc, is_ms_shipped from sys.objects where object_id in (select object_id from sys.indexes where data_space_id in (2))

— Here data_spaceid=2 represents the filegroup ID 2

2)

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]

FROM sys.indexes i

INNER JOIN sys.filegroups f

ON i.data_space_id = f.data_space_id

INNER JOIN sys.all_objects o

ON i.[object_id] = o.[object_id]

WHERE i.data_space_id = 2 — Filegroup ID

Background Info
1. SQL Server uses a proportional fill strategy as it writes to each file within the filegroup and NOT across filegroups. Read more about this in here.
2. The only exception to this could have been IF someone had changed the default filgroup to the secondary filegroup. If so, then any new objects created afterwards would have gone to the secondary instead of the primary.

Repro on SQL 2005
As any good engineer does, so did I – TESTING. To test my little theory I did the following repro on SQL 2005 :-

1) Created a new database with secondary filegroup and 1 file in that.
2) Made secondary FG as default FG for database "test".

*Note: This needs to be done as part of create database and not added later on.

3) What I found was that you DON’T NEED service broker to be enabled for the queue_messages tables to get created.
4) I found that on my test database I had 3 Internal queue_message tables which where for the following parent object

QueryNotificationErrorsQueue
EventNotificationErrorsQueue
ServiceBrokerQueue

4) All of the 3 were were of type SERVICE_QUEUE. You can find this out from sys.objects view
5) Even a new database has the same object numbers and looking at my customer’s object numbers, they are higher, which means they must have had Service Broker implemented at some point.

queue_messages_254180947
queue_messages_286181061
queue_messages_318181175

 

6) I tested this by creating a service queue when my secondary filegroup was the default filegroup.

use test

GO

CREATE QUEUE TestQueue

WITH

STATUS = OFF,

RETENTION = ON

 

7) This created the table queue_messages_2105058535 on FG 2 of type "QUEUE_MESSAGES" indicating its a SSB queue.

 

8)  To remove the internal table I did a DROP QUEUE.

drop queue TestQueue

 

9) This removed the associated internal table and the indexes. You can use the following query to identify internal tables which are for service broker queues.

select a.name as [ChildName], a.object_id as [ChildObjectID], a.type_desc as [ChildType], a.internal_type_desc as [ChildTypeDesc],

a.parent_id as [Parent_ObjectID], b.name as [Parent Name], b.type_desc as [Parent_Type]

from sys.internal_tables a

inner join sys.objects b

on a.parent_id = b.object_id

 

So the issue still does NOT reproduce on SQL 2005 (same for SQL 2008). I then did the following repro on a SQL Server 2000 instance.

Repro on SQL 2000
1. Created a database on SQL 2000
2. Added File-group (FG2) and made it as default.
3. Took a backup of the database in SQL 2000.
4. Restored this to SQL 2005/2008.
5. System Services/Queues got created on FG2.
*Note: We cannot modify them because they are system objects.

6) The remove File command gives us this error,

Alter database test remove file fil2

Msg 5031, Level 16, State 1, Line 1

Cannot remove the file ‘fil2’ because it is the only file in the DEFAULT filegroup.

 

7)  Next up, I ran these commands.

 

dbcc shrinkfile(3, EMPTYFILE)

go

alter database test remove file fil2

 

This worked so far and in sys.database_files I see the status of file2 as OFFLINE. But unfortunately I still cannot remove the filegroup. It still says that it is not empty. Even though the emptyfile commands worked, the objects (SSB queues) still exist on FG2.

I followed same steps for a database created on SQL 2008 and took a backup and restored it again on SQL 2008 and issue did not reproduce. i.e. objects were created on Primary and none of them went to the Secondary FG. I did the same test on SQL 2005 Database backed up and restored to SQL 2008. This also did NOT reproduce the issue.

So, what did I learn out of all my testing (and precious time). Read on…

My Theory
This database had to have been restored from SQL 2000 to a higher version or been upgraded in-place from SQL 2000 to higher version? Tracking down the source of the original database would help us figure this out.

If yes, we now know the answer and that this behaviour is ByDesign and understood, i.e. how the system tables went to the secondary filegroup.  This is because the non-primary filegroup FG2_fg was set as DEFAULT prior to backup from SQL 2000 or in-place upgrade. Once the upgrade was done, since SSB was newly introduced starting with SQL 2005, during the upgrade the upgrade scripts created these objects on the secondary filegroup since it was set as the default filegroup.


Verifying My Theory

To confirm if an in-place upgrade was done, we can look in sys.database_files DMV and look at the column file_GUID. The file_guid column will be NULL if the database was upgraded from an earlier version of Microsoft SQL Server. (Upgraded only, not for a Restore).

I have to thank the SQL Storage Engine folks for thinking about this kind of a scenario and capturing such detailed info in the catalog views. Thanks Folks!

I went back to my customer’s database and looked at the sys.databases output and I noticed this,

file_id

file_guid

type

type_desc

data_space_id

name

physical_name

1

08C43589-1462-4492-8778-D4BCA128ED66

0

ROWS

1

test_Data

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test_Data.mdf

2

A7ADDAF4-0425-4BB8-988C-FE260A41331C

1

LOG

0

test_Log

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test_Log.ldf

5

3542AAD9-4AC4-499A-90B8-D342D0CBFFE6

0

ROWS

1

test_Data2

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test_Data2.ndf

6

NULL

0

ROWS

2

fil2

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fil2.ndf

7

12D108E4-DEEE-4302-A61A-AD6FE21B5EF3

0

ROWS

1

test_Data3

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test_Data3.ndf

 

Notice that for data_space_id=2 which is for the secondary filegroup FG2_fg, the file_guid column has a value of NULL !!!
EUREKA!!!

This confirms that this instance/database was indeed upgraded from an earlier version of SQL 2000/7.0 to SQL (2005/2008). This behaviour is by design and we now understand how the system SSB objects were created on secondary filegroup.


Conclusion

Q: Why cannot we remove the file and filegroup?
Ans: Because of the above mentioned queue_messages objects which are present in the secondary filegroup. If there were user-created service broker queues/services then they can be Dropped or Altered to move them to the primary filegroup. Since these came as part of the database during DB Creation, they cannot be modified/dropped/moved. Hence, we cannot remove the filegroup.

Q: How did these "system" objects get to the secondary file group?
Ans: These system objects became part of the database starting with SQL Server 2005 onwards. So, If you had a database in SQL 2000 and you upgraded that to SQL 2005/2008 these system objects will get created. But, in SQL 2000 if the secondary file-group FG2_fg was set as the DEFAULT file-group, then any new objects created without an explicit MOVE TO will go the default filegroup. So when you upgrade the database to SQL 2008 these automatically got created on the secondary file-group.

Q: What data do I have to prove that the theory above is true in your case?
Ans: We store the information about each database file in sys.database_files DMV. There is a column called file_guid which when NULL indicates that the database/file was upgraded from an earlier version on SQL Server. So when I looked at sys.database_files in the database test, I saw the value for the secondary file as NULL.

Reference – http://technet.microsoft.com/en-us/library/ms174397(SQL.100).aspx

 

I spent some serious time to arrive at this conclusion and I am sharing this with the SQL community, so that it saves you some time. As always, stay tuned to theSQLDude for more…

Posted in Storage Engine | Tagged: , , , , , , | Leave a Comment »