The SQL Dude!

June 2023
M	T	W	T	F	S	S
	1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30

June 2023
M	T	W	T	F	S	S
	1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30

The curious case of public users having control server access to SQL Server

Posted by Sudarshan Narasimhan on March 28, 2012

I thought I had seen it all in SQL Server. But life finds a way of throwing a few surprises and WTF moments in your way. I happened to be working with a customer who had some Windows users and these users had complete access over SQL Server. They were able to login, access all databases & tables, create new databases, drop databases (yes!) etc. Now, these Windows logins weren’t a part of the SQL logins in sys.syslogins at all. How did they gain access? Where are they getting the complete equivalent of sysadmin permissions from?

Thus begins the story of the curious case of public logins and how Dr.Sherlock went about solving the mystery. Conan Doyle on…..

Basics

1. Check if this Windows login is part of sys.syslogins
2. Check if this Windows login is part of any Groups, that are part of the logins.
3. If so, check what permissions this group has on the instance/database and what roles these Groups belong to.

Okay, what this told me was that there were 2 Windows Groups added as logins to SQL Server, but this login was a part of only 1 group and this group AD\GroupXYZ did not have sysadmin [EXEC sys.sp_helpsrvrolemember ‘sysadmin’] or database access. ??@!#*@!#!@#!

Since my initial testing had revealed that with this login create/drop/backup etc. were possible, this login is obviously having some higher level privilege in SQL Server akin to what the SA would have. I had already checked sysadmin role and this login/group wasn’t a part of the role. There are 3 scenarios in SQL Server where logins have implied access to a database. By “implied” I mean that a login need not be mapped to any database, but this login can still have complete access to the database.

1. Part of the sysadmin server role.

2. Database OWNER aka DBO inside the database

3. CONTROL SERVER permission is granted at server level. (SQL 2005 & above only)

Not many people know or pay attention to the 3rd point about Control Server permissions. CONTROL SERVER is a new permission available starting with SQL Server 2005 and it grants the same access level as being a member of the sysadmin fixed server role. But still, I didn’t have an answer to how this login was able to access the database ProdDB. I opened a new query window with this login and started a profiler trace from my SA account adding all the “Security Audit” events to the trace, and filtering it on the SPID number from my query window connection.

Here is what I observed when I ran a “select * from dbo.employees” table.

1. The login name was “sudarn2\testuser”, which is my windows login.

2. The login was accessing my database “test” as a public user. Aha!

As you can see above, the select succeeded and I was able to read all the data as a public user! You can see each column definition for the event Audit Schema Object Access Event Class here.

This is starting to make sense now as to how the user is able to access all the databases, which being mapped to none. This user was accessing the database under the context of public user. But out-of-the-box, SQL Server definitely does not have any such permission granted to public user. This must be some customized public user then.

I use the following query to find out what Server Level permissions the public user had been granted using the DMV sys.server_permissions.

SELECT SPr.name as [UserName],SP.permission_name,SP.class_desc,'Server' AS 'Securable',SP.state_desc, SPr.type_desc as [Grantee_Type]
FROM sys.server_permissions SP
INNER JOIN sys.server_principals SPr
 ON SP.grantee_principal_id = SPr.principal_id
WHERE class = 100
AND SPr.name = 'Public'
UNION 
SELECT SPr.name,SP.permission_name,SP.class_desc,SPr2.name AS 'Securable',SP.state_desc, SPr.type_desc as [Grantee_Type]
FROM sys.server_permissions SP
INNER JOIN sys.server_principals SPr
 ON SP.grantee_principal_id = SPr.principal_id
INNER JOIN sys.server_principals SPr2
ON SP.major_id = SPr2.principal_id
WHERE class = 101 
AND SPr.name = 'Public'
UNION 
SELECT SPr.name,SP.permission_name,SP.class_desc,ep.protocol_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS 'Securable',SP.state_desc, SPr.type_desc as [Grantee_Type]
FROM sys.server_permissions SP 
INNER JOIN sys.endpoints ep
 ON SP.major_id = ep.endpoint_id
INNER JOIN sys.server_principals SPr
ON SP.grantee_principal_id = SPr.principal_id
WHERE SP.class = 105
AND SPr.name = 'Public'

Table 1. Public Server Permissions on this problematic instance

name	permission_name	class_desc	state_desc
public	CONNECT	ENDPOINT	GRANT
public	CONNECT	ENDPOINT	GRANT
public	CONNECT	ENDPOINT	GRANT
public	CONNECT	ENDPOINT	GRANT
public	CONTROL SERVER	SERVER	GRANT
public	VIEW ANY DATABASE	SERVER	GRANT
public	ADMINISTER BULK OPERATIONS	SERVER	GRANT
public	ALTER ANY CONNECTION	SERVER	GRANT
public	ALTER ANY CREDENTIAL	SERVER	GRANT
public	ALTER ANY DATABASE	SERVER	GRANT
public	ALTER ANY ENDPOINT	SERVER	GRANT
public	ALTER ANY EVENT NOTIFICATION	SERVER	GRANT
public	ALTER ANY LINKED SERVER	SERVER	GRANT
public	ALTER ANY LOGIN	SERVER	GRANT
public	ALTER ANY SERVER AUDIT	SERVER	GRANT
public	ALTER RESOURCES	SERVER	GRANT
public	ALTER SERVER STATE	SERVER	GRANT
public	ALTER SETTINGS	SERVER	GRANT
public	ALTER TRACE	SERVER	GRANT
public	AUTHENTICATE SERVER	SERVER	GRANT
public	CONNECT SQL	SERVER	GRANT
public	CONTROL SERVER	SERVER	GRANT
public	CREATE ANY DATABASE	SERVER	GRANT
public	EXTERNAL ACCESS ASSEMBLY	SERVER	GRANT
public	SHUTDOWN	SERVER	GRANT
public	UNSAFE ASSEMBLY	SERVER	GRANT
public	VIEW ANY DEFINITION	SERVER	GRANT

Table 2. Default Public Server Permissions out of the box with SQL Server

name	permission_name	class_desc	Securable	state_desc
public	CONNECT	ENDPOINT	NAMED_PIPES	GRANT
public	CONNECT	ENDPOINT	SHARED_MEMORY	GRANT
public	CONNECT	ENDPOINT	TCP	GRANT
public	CONNECT	ENDPOINT	VIA	GRANT
public	VIEW ANY DATABASE	SERVER	Server	GRANT

As you see in the Table 2 above, the public role has been granted a bunch of Server Level permissions which are un-necessary and pose a security risk. The most important of them all is the CONTROL SERVER permission, as this alone is enough to gain sufficient permissions equivalent to a sysadmin. The control server granted to public implies other permissions like ALTER ANY DATABASE, SHUTDOWN, ALTER ANY LOGIN etc. The full list of permissions implied by Control Server is given here.

So we finally have an answer to who (public) and how (control server granted to public). This was how an un-mapped user was able to access a user database like ProdDB and read/edit data etc., all because someone threw the security best practise book into the trash and decided to grant pretty much every server permission to the public role.

What to do to fix this gaping security hole?

Since we have identified the permission which grants public this unwanted access, we need to either REVOKE it or DENY it. Here is the T-SQL to do this,

REVOKE CONTROL SERVER FROM public;
(or)
DENY CONTROL SERVER TO public;

After the above command was executed by SA, the windows user is now unable to access any database he isn’t mapped to, which is what we wanted. But as a further test, we created a new SQL login called testuser and mapped it to 1 specific user database. When we tried to login using testuser, the login failed with this error.

When I checked the SQL error log, I saw the following 18456 error getting logged.

2012-03-28 23:33:33.570 Logon Error: 18456, Severity: 14, State: 11.

2012-03-28 23:33:33.570 Logon Login failed for user ‘SUDARN2\testuser’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

State:11 means the login is valid, but lacks server permissions. I used the same query above or this one below to see what permissions public role now had at the Server level.

SELECT
State_Desc, Permission_Name, class_desc,
COALESCE(OBJECT_NAME(major_id),DB_NAME(major_id)) SecurableName, SCHEMA_NAME(O.schema_id) [Schema],
Grantees.Name GranteeName, Grantees.Type_Desc GranteeType
FROM sys.server_permissions Perms
INNER JOIN sys.server_principals Grantees ON Perms.Grantee_Principal_Id = Grantees.Principal_Id
LEFT OUTER JOIN sys.all_objects O ON Perms.major_id = O.object_id
where Grantees.Name = 'public'
ORDER BY GranteeName

I saw the after revoking the CONTROL SERVER permissions, the other implied permissions were gone and now only the “VIEW ANY DATABASE” permissions was granted. Going back to Table 2. above we know that by default SQL Server has granted 5 Server permissions to public and out of this 4 of them are on ENDPOINTS. One of each protocol type: TCP, Named Pipes, Shared Memory and VIA. These were missing.

I granted these back so as to reset public back to the original out-of-box configuration.

grant CONNECT ON ENDPOINT::[TSQL Named Pipes] to public;
grant CONNECT ON ENDPOINT::[TSQL Local Machine] to public;
grant CONNECT ON ENDPOINT::[TSQL Default TCP] to public;
grant CONNECT ON ENDPOINT::[TSQL Default VIA] to public;

Once I did this, the test login testuser was able to successfully make a connection to SQL Server.

Notes:

1. For those of you DBA’s out there, who don’t want any user/login accessing the SQL Server instance, the 4 endpoint permissions above would be an ideal candidate to revoke from public. Once this is done, no one except sysadmins can connect to SQL Server.

2. You can revoke these 4 endpoint permissions using the below T-SQL commands,

Revoke CONNECT ON ENDPOINT::[TSQL Named Pipes] From public;
Revoke CONNECT ON ENDPOINT::[TSQL Local Machine] From public;
Revoke CONNECT ON ENDPOINT::[TSQL Default TCP] From public;
Revoke CONNECT ON ENDPOINT::[TSQL Default VIA] From public;

3. Just running the above is not enough, since you need to take care of granting connect permissions to your actual application logins, e.g. db_appadmin. If you revoke as given in step #2 and stop there, then you will get into deep trouble and none of your application users will be able to connect to SQL Server. The login will fail with Msg: 18456, State:11. You will need to specifically grant connect on endpoint permissions to each of those logins (for all the 4 protocols) which needs to connect to the SQL instance.

GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [loginname]
e.g. GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [db_appadmin]

Well, thus ends the strange but interestingly curious story of public role access and how to control it. I have some more T-SQL code and samples covering public & Guest users. Read through this blog post of mine for more details on how to go about curbing public rights and its pros & cons.

It’s all elementary my dear Watson Smile .

Posted in Security | Tagged: guest, login failed, logins, permissions, prevent public access, public, security, SQL, unauthorized access | 2 Comments »

T-SQL Auditing Script to find logins inheriting permission via AD Groups and verify DBO/Database owner login in a single execution

Posted by Sudarshan Narasimhan on March 5, 2012

I had authored a post on this a couple of weeks back to detect mismatch between DBO User & database owner. I had also included how to find the various groups a windows login belongs to using the login’s token in sys.login_token. I continued working on this script and finished it last week. One of the problem’s was the login token view is only useful when you have logged in via the User itself. If you are an admin you will get results from the Admin login token, which isn’t useful in detecting permissions.

So, this new script I wrote will give you the following information, which is very very useful when doing security audits, security permission validation and especially useful if you manage a lot of databases and your SQL Server has a lot of AD Group logins etc. Here is what my script does:-

DBO Owner Login to Database Owner Mapping.
Logins which are part of AD Groups and has DBO rights in each database
Members of DB_OWNER role in each database excluding the DBO user itself (including permissions via AD Groups).
Summary: Identify those users who are DBO mapped logins, but they do NOT have explicit login NOR are they are part of DB_OWNER role.

You can think of this as a complete security auditing script for AD Logins, groups, Database Owner/DBO validity.

T-SQL SCRIPT for Security Auditing of DBO Access & AD Groups

set nocount on
go
Create table #TmpTableSec1 (database_name varchar(100), Owner varchar(100))
Create table #TmpTableSec2 (database_name varchar(100), principal varchar(50), DBO_Owner_Login varchar(100))
Create table #TmpResult (database_name varchar(100), principal varchar(50), DBO_Owner_Login varchar(100))
DECLARE DBCURSOR CURSOR FOR
select name 
from sys.databases where state=0 and name not in ('tempdb','model','master','msdb')
Declare @name varchar(50)
Declare @cmd  varchar(200)
Declare @dbowner varchar(100)
OPEN DBCURSOR
FETCH NEXT FROM DBCURSOR INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
      --print 'Database --> ['+@name+']'
      set @cmd = 'select name,  suser_sname(owner_sid) from master.sys.databases where name = '''+@name+''''
      --select @cmd
      insert #TmpTableSec1 exec (@cmd)
      --select @dbowner = (select suser_sname(owner_sid) from master.sys.databases where name = @name)
      set @cmd = 'use '+ @name +'
      select db_name(), name, suser_sname(sid)
      from sys.database_principals where name = ''dbo'''
      --select ''@DBO'' = (select suser_sname(sid) from sys.database_principals where name = ''dbo'') '
      INSERT #TmpTableSec2 exec (@cmd) 
FETCH NEXT FROM DBCURSOR INTO @name
END
CLOSE DBCURSOR
DEALLOCATE DBCURSOR
--select * from #TmpTableSec1
--select * from #TmpTableSec2
insert into #TmpResult
select a.database_name, a.Owner, b.DBO_Owner_Login from #TmpTableSec1 a
join #TmpTableSec2 b
on a.database_name = b.database_name
--select * from #TmpResult

Create table #GroupMemberTable (account_name varchar(200), type varchar(50), privilege varchar(100), login_name varchar(200), group_name varchar(500))

DECLARE DBCURSOR2 CURSOR FOR
select distinct(DBO_Owner_Login)
from #TmpResult where DBO_Owner_Login like '%\%' and DBO_Owner_Login <> 'NT AUTHORITY\SYSTEM'
Declare @DBO varchar (200)
Declare @DBNAME varchar (200)
OPEN DBCURSOR2
FETCH NEXT FROM DBCURSOR2 INTO @DBO
WHILE @@FETCH_STATUS = 0
BEGIN
      set @cmd = 'EXEC xp_logininfo '''+@DBO+ ''', ''all'' '
      insert into #GroupMemberTable EXEC (@cmd)
      --update #GroupMemberTable set database_name = @DBNAME where id=@@IDENTITY 
      --print @cmd
FETCH NEXT FROM DBCURSOR2 INTO @DBO
END
CLOSE DBCURSOR2
DEALLOCATE DBCURSOR2
--select * from #TmpResult
--select * from #GroupMemberTable where group_name <> 'NULL' --and privilege = 'admin'

Create table #Tmp_DB_ownerRoleResults (database_name varchar(100), DBOwnerRole_Member_UserName varchar(200), User_type varchar(100))
DECLARE DBCURSOR3 CURSOR FOR
select database_name
from #TmpResult where DBO_Owner_Login <> 'NT AUTHORITY\SYSTEM'
Declare @database_name3 varchar (200)
Declare @cmd3  varchar(500)
OPEN DBCURSOR3
FETCH NEXT FROM DBCURSOR3 INTO @database_name3
WHILE @@FETCH_STATUS = 0
BEGIN
      set @cmd3 = 'use '+ @database_name3 +'
      select db_name(), user_name(member_principal_id), type_desc from sys.database_role_members a
      inner join sys.database_principals b
      on a.member_principal_id = b.principal_id where a.role_principal_id=16384'
      --print @cmd3
      --exec (@cmd3)
      insert into #Tmp_DB_ownerRoleResults exec (@cmd3)
FETCH NEXT FROM DBCURSOR3 INTO @database_name3
END
CLOSE DBCURSOR3
DEALLOCATE DBCURSOR3

print '###########################################################'
print '###### Database Owner and DBO Owner Logging Mapping #######'
print '###########################################################'
select * from #TmpResult


print '###########################################################################'
print '###### AD Group Member Logins which have DBO Access in the database #######'
print '###########################################################################'
create table #TmpTableSec3 (database_name varchar(100), Database_Owner varchar(200), DBO_Owner_Login varchar(200), type varchar (100), privilege varchar(100), group_name varchar(500))
insert into #TmpTableSec3
select a.database_name, a.principal, a.DBO_Owner_Login, b.type, b.privilege, b.group_name
from #TmpResult a
left join #GroupMemberTable b
on a.DBO_Owner_Login = b.login_name
where b.group_name is not null --and b.privilege = 'admin'
order by a.database_name
select database_name, DBO_Owner_Login, privilege, group_name  from #TmpTableSec3 
where privilege = 'admin' order by database_name asc

print '####################################################################################'
print '###### Members of DB_OWNER ROLE For Each Database (Excluding DBO Owner Login)#######'
print '####################################################################################'
Create table #TmpTableSec4 (database_name varchar(100), DBOwnerRole_Member_UserName varchar(200), User_type varchar(100))
insert into #TmpTableSec4
select a.database_name as [Database_Name], b.DBOwnerRole_Member_UserName, b.User_type 
from #TmpResult a
inner join #Tmp_DB_ownerRoleResults b
on a.database_name = b.database_name
where b.DBOwnerRole_Member_UserName <> 'dbo'

Create table #TmpTableSec5 (database_name varchar(100), DBOwnerRole_Member_UserName varchar(200), User_type varchar(100))
insert into #TmpTableSec5
select a.name as [Database_Name], b.DBOwnerRole_Member_UserName, b.User_type 
from sys.databases a
left outer join #TmpTableSec4 b
on a.name = b.database_name

select * from #TmpTableSec5
where DBOwnerRole_Member_UserName is not null
order by database_name

print''
print '#########################################################################################################################'
print '###### Final Result: Users who are not part of DB_OWNER Role nor having explicit login access but have DBO rights #######'
print '#########################################################################################################################'
print ''
create table #TmpFinalResult (group_name varchar(500), database_name varchar(100), DBO_Owner_Login varchar(200), Database_Owner varchar(200), privilege varchar(100))
insert into #TmpFinalResult
select distinct (a.group_name), a.database_name, a.DBO_Owner_Login, a.Database_Owner, a.privilege
from #TmpTableSec3 a
where a.database_name in (select database_name from #TmpResult where principal <> DBO_Owner_Login)
and a.group_name not in (select name from sys.syslogins where sysadmin = 1 and isntgroup = 1)
and a.DBO_Owner_Login not in (select name from sys.syslogins where sysadmin = 1 and isntuser = 1)
and a.DBO_Owner_Login not in (select DBO_Owner_Login from #TmpTableSec3 where privilege = 'admin')
--and a.group_name not in ('Any Domain Groups to be filtered goes here e.g. [DOMAIN\All Users]')
order by a.database_name asc
select a.database_name, a.DBO_Owner_Login, a.Database_Owner, a.group_name, a.privilege from #TmpFinalResult a
order by a.database_name asc

drop table #TmpTableSec1
drop table #TmpTableSec2
drop table #TmpTableSec3
drop table #TmpTableSec4
drop table #TmpTableSec5
drop table #TmpResult
drop table #GroupMemberTable
drop table #Tmp_DB_ownerRoleResults
drop table #TmpFinalResult
go
set nocount off
go

Explanation of the Script Output

###########################################################

###### Database Owner and DBO Owner Logging Mapping #######

###########################################################

This section gives you the mapping between the database owner in sys.databases and the login which owns the DBO User in each database. If you move databases between servers, its possible that the old DBO Login doesn’t exist on the new instance and this could cause unauthorized access by the old DBO id he/she has login access to the server, then he/she retains DBO access in the newly moved database.

###########################################################################

###### AD Group Member Logins which have DBO Access in the database #######

###########################################################################

This section presents to you the different Active Directory Groups that have DBO/DB_Owner access in each database. Sometimes, if you have too many AD Groups in different OU’s, for a DBA it gets confusing to manage the authorized access. You can certainly manually look up the AD Users and figure this out. But, being a SQL person I’m sure its a lot more comfortable seeing this within SQL Server itself. This script does just that and shows the diff groups per each database.

####################################################################################

###### Members of DB_OWNER ROLE For Each Database (Excluding DBO Owner Login)#######

####################################################################################

Since we are anyways getting DBO Owner login we might as well detect the different users who are part of DB_OWNER fixed database role. Each member of DB_OWNER role has Full Control on the database. Hence the need to track this information.

#########################################################################################################################

###### Final Result: Users who are not part of DB_OWNER Role nor having explicit login access but have DBO rights #######

#########################################################################################################################

This is the one that does the magic! It gives a final report of each user who has DBO Access, but who isn’t a part of DB_OWNER role nor does this person has sysadmin through any of the AD Groups. It also reports all the AD Group, through which this windows user can get a login access to SQL Server thereby gaining DBO Access when switching context to the database reported against the user’s name. If you see any results here, then its very much a security hole, that needs to be plugged after careful review of this user’s individual object rights.

The commented line on a.group_name in the final section, is for you to add any AD Groups which each user in the organisation is a part of, thereby removing any unnecessary rows from the output.

Have fun Auditing. Please post back your comments on the security script…

Posted in Security | Tagged: Active Directory, AD Groups, Auditing, database owner, DBO Access, login token, security, SQL | 3 Comments »

Verifying DBO User ownership and track users without a SQL login but via AD Group Logins

Posted by Sudarshan Narasimhan on February 18, 2012

I was working with one of my customers and came across a unique scenario, so I thought it share it out here. My customer had multiple Active Directory groups which were added as logins in SQL Server. Some of these groups had access to only certain databases and some had sysadmin rights. It so happens that certain users were part of multiple AD groups, so we need to track down the security chain to see which of these groups were granting access & permissions to these databases.

Now, if a Windows login is part of multiple groups, then during the initial login phase, the access token is created. This access token contains all the groups that this login is a part of. There are 2 possible scenarios here:-

1. The Windows user is also a login in SQL Server as well as the Group is a login.

2. The Windows user does not have a login in SQL Server but only via the Group.

In case #1, the user will access based on the login’s access rights. In case #2, if the user were part of multiple groups how do we determine which group was granting his permissions inside a database.

1. Read the login Token

Luckily, we have a view that lets you see what groups are part of a login’s token called as sys.login_token. This displays the login token for the currently logged in session. We need to filter this to report only those groups which are in the login’s token and which are also a valid login in SQL Server.

select * from sys.login_token where sid in (select sid from sys.server_principals)

Once you have this information, you can then focus on these groups listed here to see what access rights they have on the database in question. Here is a sample query to check your access rights on objects in a database.

select @@SERVERNAME '@@servername', db_name() 'dbname',
SYSTEM_USER 'system_user', CURRENT_USER 'CURRENT_USER', session_user 'session_user' ,  SUSER_SNAME()  'SUSER_SNAME()',
* from fn_my_permissions (null, 'database')

2. DBO User and Login Mapping

In SQL Server each database has a user called as ”DBO”. This is a built-in user who is the owner of the database. The DBO user has full-control of the database and the access rights are not explicitly visible on each database, but understood to be granted. So if my windows login CONTOSO\testuser was the owner of database “AdventureWorks”, then within this database AdventureWorks, my login contoso\testuser will be known as DBO. There can only be 1 DBO user in a database. Don’t get confused with db_owner database role. This role can be used to grant other logins dbo rights, meaning all access rights are the same, but those logins will be added as users to the database. But the database owner itself will not have a user inside the database, because he is the DBO user.

Using SQL Management Studio, we can find the login which is mapped to the DBO user in a database,

There is 1 scenario, where the DBO user can be mapped to an invalid login. If the database was attached from one server where loginA was the DBO and the moved to another server where loginA does not even exist, then you will see that loginA is still the DBO. Of course, during the attach you can change the db_owner, but this is not something that people generally do.

You can use the following script to identify all the databases whose DBO user is mapped to an invalid login. Once identified you will have to correct is using any of the below commands.

use <dbname>
sp_changedbowner 'sa'

use <dbname>
ALTER AUTHORIZATION ON DATABASE::AdventureWorks TO [CONTOSO\testuser]

T-SQL Script

--Script : Verify the database owner and DBO User owner are the same
--When a login is defined as the DBO user, he/she has FULL control within the database
set nocount on
go
Create table #TmpTableSec1 (database_name varchar(100), Owner varchar(100))
Create table #TmpTableSec2 (database_name varchar(100), principal varchar(50), DBO_Owner_Login varchar(100))
Create table #TmpResult (database_name varchar(100), principal varchar(50), DBO_Owner_Login varchar(100))
DECLARE DBCURSOR CURSOR FOR
select name 
from sys.databases where state=0 and name not in ('tempdb','model','master','msdb')
Declare @name varchar(50)
Declare @cmd  varchar(200)
Declare @dbowner varchar(100)
Declare @DBO varchar (100)
OPEN DBCURSOR
FETCH NEXT FROM DBCURSOR INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
    --print 'Database --> ['+@name+']'
    set @cmd = 'select name,  suser_sname(owner_sid) from master.sys.databases where name = '''+@name+''''
    --select @cmd
    insert #TmpTableSec1 exec (@cmd)
    --select @dbowner = (select suser_sname(owner_sid) from master.sys.databases where name = @name)
    set @cmd = 'use '+ @name +'
    select db_name(), name, suser_sname(sid)
    from sys.database_principals where name = ''dbo'''
    --select ''@DBO'' = (select suser_sname(sid) from sys.database_principals where name = ''dbo'') '
    INSERT #TmpTableSec2 exec (@cmd)
    --print ''
FETCH NEXT FROM DBCURSOR INTO @name
END
CLOSE DBCURSOR
DEALLOCATE DBCURSOR
go
insert into #TmpResult
select a.database_name, a.Owner, b.DBO_Owner_Login from #TmpTableSec1 a
join #TmpTableSec2 b
on a.database_name = b.database_name
print '--- Database Owner & DBO User Mapping ---'
print '*****************************************'
select * from #TmpResult
print '--- Orphaned DBO Logins ---'
print '***************************'
select * from #TmpResult
where DBO_Owner_Login not in (select name from sys.server_principals) or  DBO_Owner_Login is null
drop table #TmpTableSec1
drop table #TmpTableSec2
drop table #TmpResult
set nocount off

In the result, pay careful attention to the second output as that lists all the DBO users whose login does not exist on this instance of SQL Server, along with the database name. You will need to correct that using the any of the 2 methods listed above for each of the database listed in the 2nd output.

That’s all for now folks. Stay safe, stay secure!

Posted in Security | Tagged: DBO, login group, security, sp_changedbowner, SQL Server, token, user, Windows Group | 2 Comments »

TSQL script to find Foreign Key Dependencies on entire database

Posted by Sudarshan Narasimhan on February 14, 2012

Often in the life of a DBA/Developer when we have to test something on a database, we are not always aware of the schema/design since it was created by someone else or you inherited the project. Lets says you want to test a particular SP execution time, but the data sample is too big for you to wait for the restore, so you want to insert some sample data. More often that not, the constraints defined do not let you to modify/add/remove data from the base tables. Well, you can’t pull up the entire database diagram and start studying the design.

It is not practical to do this on the entire database with Management Studio even though it offers a nice View dependencies option, but this is on a table by table basis. You would need to use T-SQL for this.

Here is a script which you can run on the entire database to understand which tables are dependent on your Table X,Y,Z and what are the columns being referenced (foreign keys) and what is the action on update/delete. You can uncomment the filter in the WHERE clause if you want to specify only some table names.

Script Modified: April 18, 2012

SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(fk.referenced_object_id), clm1.name) as 'S.No',
       fk.referenced_object_id as ReferencedObjID,
       constraint_column_id as ColumnID,
       OBJECT_NAME(fk.referenced_object_id) as [ReferencedTable(Parent)],
       SCHEMA_NAME (CAST(OBJECTPROPERTYEX(fk.referenced_object_id,N'SchemaId') AS tinyint)) as [ParentSchema],
       clm2.name as ReferencedColumnName,
       OBJECT_NAME(constraint_object_id) as ConstraintName,
       fk.parent_object_id as ReferencingObjID,
       OBJECT_NAME(fk.parent_object_id) as [ReferencingTable (Foreign)],
       clm1.name as ForeignKeyColumn,
       SCHEMA_NAME (CAST(OBJECTPROPERTYEX(fk.parent_object_id,N'SchemaId') AS tinyint)) as [ForeignSchema],
       [Action on Update] = CONVERT(varchar,CASE OBJECTPROPERTY(constraint_object_id,'CnstIsUpdateCascade')  
                                        WHEN 1 THEN 'CASCADE'
                                        ELSE 'NO_ACTION'
                                      END), 
       [Action on Delete] = CONVERT(varchar,CASE OBJECTPROPERTY(constraint_object_id,'CnstIsDeleteCascade')  
                                        WHEN 1 THEN 'CASCADE'
                                        ELSE 'NO_ACTION'
                                      END)
FROM sys.foreign_key_columns fk
       JOIN sys.columns clm1 
         ON fk.parent_column_id = clm1.column_id 
            AND fk.parent_object_id = clm1.object_id
       JOIN sys.columns clm2
         ON fk.referenced_column_id = clm2.column_id 
            AND fk.referenced_object_id= clm2.object_id
 --WHERE OBJECT_NAME(fk.referenced_object_id) = 'TBL_TEST'  --- table name which is being referenced by other tables via Foreign Keys
 ORDER BY OBJECT_NAME(fk.referenced_object_id)

Here is the how the output looks like on the AdventureWorks database

Posted in T-SQL | Tagged: dependency, foreign keys, INFORMATION_SCHEMA, referenced, referencing, SQL Server, sys.foreign_keys | 9 Comments »

SQL Server Ring Buffers and The Fellowship of the Ring

Posted by Sudarshan Narasimhan on January 31, 2012

I like SQL Server Ring Buffers. They are very handy in troubleshooting specific issues like out-of-memory conditions, login failures and connectivity related errors. Yet, they remain a much under utilized part of a DBA’s repertoire or toolkit. This might be due to the fact that ring buffer output is stored in XML format and it takes a bit of T-SQL work to get them into a readable format. In this post, I will provide you with some of queries which I use to pull out information from certain ring buffer types. FYI – the DMV for ring buffers is sys.dm_os_ring_buffers

Like I always say, One Ring to find them and in the darkness bind them.

1. Security Ring Buffer

Starting with SQL Server 2005 SP2 a Security ring buffer for error logging has been added called ‘RING_BUFFER_SECURITY_ERROR’. This was specifically added to aid the DBA in finding more information regarding login failures and similar errors. On an active server, you might find multiple entries in the DMV if you filter on this ring buffer type, and an entry does not necessarily mean a failure. Let me show you how to use this with an example.

I have a user who is not able to login to SQL Server. This is the error from SSMS.

I used the following query filtering on RING_BUFFER_SECURITY_ERROR,

SELECT 
    CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
    DATEADD (ms, rbf.[timestamp] - tme.ms_ticks, GETDATE()) as [Notification_Time],
    CAST(record as xml).value('(//SPID)[1]', 'bigint') as SPID,
    CAST(record as xml).value('(//ErrorCode)[1]', 'varchar(255)') as Error_Code,
    CAST(record as xml).value('(//CallingAPIName)[1]', 'varchar(255)') as [CallingAPIName],
    CAST(record as xml).value('(//APIName)[1]', 'varchar(255)') as [APIName],
    CAST(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id],
    CAST(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
    CAST(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],
    tme.ms_ticks as [Current Time]
from sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
where rbf.ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR' --and cast(record as xml).value('(//SPID)[1]', 'int') = XspidNo
ORDER BY rbf.timestamp ASC

Output

Once you have this you can look at the SPID number and correlate this with the login failed event in the errorlog and get the Error code which is a Win32 error and translate that using net helpmsg or using http://msdn2.microsoft.com/en-us/library/ms681381.aspx. The error “0x89B” is in Hex and translates to Win32 Error 2203, which means – “The password parameter is invalid”.

This is true, since I did provide an incorrect password when logging in. Quite a simple example, but you can see the wealth of information available with the ring buffers. The security ring buffer provides the following information:-

SPID
API Name
Error Code returned by the failing API (step #2).

2. Connectivity Ring Buffer

The connectivity ring buffer was introduced starting with SQL Server 2008 and is called called ‘RING_BUFFER_CONNECTIVITY’. This will automatically capture server-side initiated connection closures, if you see nothing in the dmv, then most likely the client reset/closed the connection. You can enable any connection closure (client or server) logging with trace flag 7827 (caution advised with this trace flag). When I say server initiated closures, it also includes things like Session killed or Login Failed. More details on this is given in this blog.

Lets look at an example,

1. I opened a connection to SQL Server using sqlcmd from a remote machine.

2. I went ahead and killed this session 55 from another connection. KILL 55

3. Here is the the T-SQL query to pull out information from the connectivity ring buffer,

    SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
    dateadd (ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) as Time_Stamp,
    cast(record as xml).value('(//Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(50)') AS [Action], 
    cast(record as xml).value('(//Record/ConnectivityTraceRecord/RecordSource)[1]', 'varchar(50)') AS [Source], 
    cast(record as xml).value('(//Record/ConnectivityTraceRecord/Spid)[1]', 'int') AS [SPID],
    cast(record as xml).value('(//Record/ConnectivityTraceRecord/RemoteHost)[1]', 'varchar(100)') AS [RemoteHost],
    cast(record as xml).value('(//Record/ConnectivityTraceRecord/RemotePort)[1]', 'varchar(25)') AS [RemotePort],
    cast(record as xml).value('(//Record/ConnectivityTraceRecord/LocalPort)[1]', 'varchar(25)') AS [LocalPort],
    cast(record as xml).value('(//Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferError)[1]', 'varchar(25)') AS [TdsInputBufferError],
    cast(record as xml).value('(//Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsOutputBufferError)[1]', 'varchar(25)') AS [TdsOutputBufferError],
    cast(record as xml).value('(//Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferBytes)[1]', 'varchar(25)') AS [TdsInputBufferBytes],
    cast(record as xml).value('(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/PhysicalConnectionIsKilled)[1]', 'int') AS [isPhysConnKilled], 
    cast(record as xml).value('(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/DisconnectDueToReadError)[1]', 'int') AS [DisconnectDueToReadError],
    cast(record as xml).value('(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/NetworkErrorFoundInInputStream)[1]', 'int') AS [NetworkErrorFound],
    cast(record as xml).value('(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/ErrorFoundBeforeLogin)[1]', 'int') AS [ErrorBeforeLogin],
    cast(record as xml).value('(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/SessionIsKilled)[1]', 'int') AS [isSessionKilled],
    cast(record as xml).value('(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalDisconnect)[1]', 'int') AS [NormalDisconnect],
    cast(record as xml).value('(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalLogout)[1]', 'int') AS [NormalLogout],
    cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id], 
    cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type], 
    cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],
    tme.ms_ticks as [Current Time]
FROM sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
where rbf.ring_buffer_type = 'RING_BUFFER_CONNECTIVITY' and cast(record as xml).value('(//Record/ConnectivityTraceRecord/Spid)[1]', 'int') <> 0
ORDER BY rbf.timestamp ASC

Here is what I find in the ring buffer,

Again you can see the good amount of diagnostic information available to get more information on why this connection was closed. Some things available are :-

SPID
Remote Host Machine IP and Port number
Network Error (True/False)
Session Killed (True/False)

In this example, since the session was killed, that 4th flag is set to True. There are other scenarios like this you can troubleshoot like Login Timeout due to delay in login process, DC errors when authenticating a Windows login etc. If you don’t find the SPID in the above connectivity ring buffer output this means that the server has seen a connection closure (reset) similar to normal connection closure from the client (e.g. sp_reset_connection). Client side connectivity errors can also be made to log in the ring buffer using DBCC TRACEON (7827, -1). Again, caution advised before enabling this since it is a global trace flag applicable for the entire instance.

3. Exception Ring Buffer

This is a generic ring buffer type that records internal errors and any exceptions in SQL Server. SQL itself uses this ring buffer internally for errors, so this is most likely to always contain some information. Any errors in SQL Server are put into this generic ring buffer. TDS errors, memory errors etc. I didn’t even attempt a repro and just queried it using this T-SQL,

SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
    dateadd (ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) as Time_Stamp,
    cast(record as xml).value('(//Exception//Error)[1]', 'varchar(255)') as [Error],
    cast(record as xml).value('(//Exception/Severity)[1]', 'varchar(255)') as [Severity],
    cast(record as xml).value('(//Exception/State)[1]', 'varchar(255)') as [State],
    msg.description,
    cast(record as xml).value('(//Exception/UserDefined)[1]', 'int') AS [isUserDefinedError],
    cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id],
    cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type], 
    cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],
    tme.ms_ticks as [Current Time]
from sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
cross join sys.sysmessages msg
where rbf.ring_buffer_type = 'RING_BUFFER_EXCEPTION' --and cast(record as xml).value('(//SPID)[1]', 'int') <> 0--in (122,90,161,179)
and msg.error = cast(record as xml).value('(//Exception//Error)[1]', 'varchar(500)') and msg.msglangid = 1033 --and [Error] = 4002
ORDER BY rbf.timestamp ASC

Here is what I got,

4. Resource Monitor Ring Buffer

This ring buffer type stores information about system health conditions like memory pressure including physical and virtual. We can use ‘RING_BUFFER_RESOURCE_MONITOR’ to extract information about the condition of OS memory and SQL memory using a query like the following. Looking at this query, you will be able to easily determine the various indicators that would have triggered the Windows to page various processes including SQL Server and also get to know if there is a system memory pressure going on and how much memory SQL was consuming at a particular point in time.




SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
    dateadd (ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) as [Notification_Time], 
    cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type], 
    cast(record as xml).value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %], 
    cast(record as xml).value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id], 
    cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS [Process_Indicator],
    cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS [System_Indicator],
    cast(record as xml).value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB], 
    cast(record as xml).value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB], 
    cast(record as xml).value('(//Record/MemoryNode/AWEMemory)[1]', 'bigint') AS [SQL_AWEMemory], 
    cast(record as xml).value('(//Record/MemoryNode/SinglePagesMemory)[1]', 'bigint') AS [SinglePagesMemory], 
    cast(record as xml).value('(//Record/MemoryNode/MultiplePagesMemory)[1]', 'bigint') AS [MultiplePagesMemory], 
    cast(record as xml).value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB], 
    cast(record as xml).value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB], 
    cast(record as xml).value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB], 
    cast(record as xml).value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB], 
    cast(record as xml).value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB], 
    cast(record as xml).value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB], 
    cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id], 
    cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
    cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],
    tme.ms_ticks as [Current Time]
FROM sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
where rbf.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' --and cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') = 'RESOURCE_MEMPHYSICAL_LOW'
ORDER BY rbf.timestamp ASC

If you are interested only in low memory notifications, uncomment the filter condition in the where clause and that will display only those entries when SQL server received the low memory notification from the Operating System.

The best thing about ring buffers is that they are fast and stored on the fly. Even during low memory conditions you can still login to SQL Server using the DAC connection and query these ring buffers. Think how much faster it is as compared to collection traces or perfmon logs etc.

That’s all for now folks. You can start playing with ring buffers to understand more. Have fun!

Posted in T-SQL | Tagged: 18456, errors, exception, login failed, monitoring, ring buffer, SQL Server, timeout | 3 Comments »

SQL Server Setup: How to validate downloaded image/media before installation

Posted by Sudarshan Narasimhan on January 27, 2012

You might be wondering why I am posting about media validation. Isn’t this a given thing, after all you are downloading straight from downloads.microsoft.com, so there should be no issues with the downloaded media (.ISO), right? Wrong!

Even if you are downloading from a trustworthy source, always ensure that you have validated the media once the download is complete. For SQL Server ISO images which you downloaded from MSDN or ordered a DVD, it is a best practise to verify that the media is clean & complete. Don’t believe me, have a look at this article for the sort of setup issues like MSI Error: 2337, that you can run into, if you don’t want to spend 5 minutes to verify media integrity 🙂

Various errors may occur when you try to install SQL Server 2008 R2
http://support.microsoft.com/kb/2449398

Save yourself the time & trouble with troubleshooting a failed setup. Do the following simple steps to validate SQL Server setup media:-

1. Verify ISO is valid

A) Once you have downloaded an ISO from MSDN, use Microsoft File Checksum Integrity Verifier aka FCIV.exe to verify the checksum. This tool computes and verifies the hashes of any file and will give you a MD5 or SHA1 hash key as the output.

Lets say you downloaded the media file for SQL Server 2008 R2 Standard Edition en_sql_server_2008_r2_standard_x86_x64_ia64_dvd_521546.iso to C:\SQL\ folder from MSDN website.

Download FCIV.exe from the above link and use it as shown below to validate the above ISO file.

Note down the SHA1 hash value highlighted above.

B) Login to your MSDN subscription and go to the Subscriber Downloads page and identify the package with same version and edition you downloaded. Click on the Details button and you will see a SHA1 hash value displayed there, like shown below.

This value (step A) should match the one of your downloaded ISO package (step B), which you found out using FCIV.exe If it does, then the downloaded package is valid and has no errors/issues/inconsistencies in files etc. You can begin setup using this package and it is a valid & verified stable ISO package.

2. Extraction from the ISO Package

The most common practice once you have downloaded an ISO image is to extract its contents to a folder structure. Most of you will have to perform these installations on multiple machines and you will extract this and put it on a common file share. I have seen issues where the extraction of these ISO packages sometimes causes inconsistencies in the media like missing files, invalid files etc. This is usually brought about by the software you used to extract from the ISO like WinRAR, WinZip, MagicISO and others.

Q. What is the correct software to use to extract this media?
Ans. My favourite answer – “It depends”. Smile

Let me explain. I am not going to suggest one piece of software over another. Instead, I’ll take a different approach to this issue.

1. Extract the verified ISO package using any of the above mentioned software. Lets assume that the folder which contains the extracted media is called “C:\SQL\SQL2008R2Media”.

2. Use FCIV.exe to generate a hash database for this extracted folder, like show below,

fciv.exe -add "C:\SQL\SQL2008R2Media" -r -XML C:\SQL\db.xml

What this does is computes a SHA1 hash for each file under this directory and stores it in the db.xml file. This is the way to validate an entire directory and its contents.

3. Install the SQL Server using this media. It is a successful installation and things go fine, then you know 100% that this media copy/folder/package is a good one to use for further installations.

This also proves that the extraction software used in step #1 above can be used again (same version of the s/w to be specific).
In future if you are copying this media for installation to any other machine or USB drive or on DVD etc etc, you have to validate the new destination by comparing it with the db.xml that we created above.

Here is how you do that,

Location of SQL Server directory: C:\Program Files\Microsoft SQL Server\
Location of my db.xml file: C:\SQL

Navigate to the SQL Server folder in the command prompt
1) cd C:\Program Files\Microsoft SQL Server\
2) fciv -v -xml C:\test\db.xml

where –v is to verify the hashes and –xml is to use the db.xml which we know is a good source and compare that for the files in the SQL Server folder.

That’s all there is to it. Your SQL Server media is verified clean and green. Don’t forget to put a ISO stamp on it Winking smile . Stay tuned for more SQL’lly stuff.

Posted in Setup | Tagged: 2449398, Could not find the Database Engine startup handle, download, failed, fciv, hash, ISO, kb, media, SQL Server | 8 Comments »

Update to the MDW (Data Collector) Post

Posted by Sudarshan Narasimhan on January 13, 2012

Back in August 2011, I had posted quite a lengthy blog post on Management Data Warehouse (Data Collector). I made an update to that post today to include a new issue and the fix for that issue. This has also been updated in the official KB article given below. For those of you out there using Data Collector to monitor your SQL Server’s, please read this to avoid any future issues.

FIX: Data Collector job takes a long time to clear data from a MDW database in SQL Server 2008 R2
http://support.microsoft.com/kb/2584903

To break it down (or) putting it simply, here is what you need to do as a DBA IF you are using MDW or Data Collector

1. For SQL Server 2008 R2, please apply Service Pack 1 immediately.
2. Follow KB 2584903 (or) my earlier post to modify the stored procedure sp_purge_orphaned_notable_query_text.
3. Run the purge procedure ad-hoc or let it run on schedule and this will actually do the clean-up and reduce the size of the MDW database.

The reason for step#2 even after applying SP1, is because a new bug in the T-SQL code was found. i.e. the delete statement incorrectly references the snapshots.notable_query_plan table after you apply Service Pack 1, and we need to change this to correctly reference the snapshots.notable_query_text table.

Have fun!

Posted in Data Collector | Tagged: Data collector, deadlock, ExecMasterPackage, KB 2584903, long, MDW, purge, slow, timeout | Leave a Comment »

SQL Saturday # 116: Be there, it’s the real deal!

Posted by Sudarshan Narasimhan on January 7, 2012

For all SQL Server folks in India, please bookmark your calendar for the SQL Saturday #116 (organised by PASS and Microsoft) as this is the first time a SQL Saturday event is being organized in Bangalore at the Microsoft Office on January 7th, 2012.

I will be attending this event and will also be a part of the SQL Server Clinic, which starts @ 2:30 pm – 5:30 pm IST. SQL Clinic is an open event where you, your colleagues and other SQL enthusiasts can come, meet, discuss and ask questions to MSFT CSS engineers. I will be one of those engineers whom you can pester with any questions on SQL Server Smile . You are free to bring real-world SQL problems that you face in your work and we will be happy to shed some light on them. If you have a specific SQL problem that has been bugging you for weeks, this is THE Forum to discuss them! It helps if you bring the details like logs, traces, dump files etc., as live troubleshooting and analysis is very much a part of SQL Clinic. Come, join in…

Directions to the Microsoft GTSC Campus in Bangalore

Bing Maps Link

Google Maps Link

A favourite quote of mine
– “Unfortunately, no one can be told what the Matrix is. You have to see it for yourself. So in a world of 1s and 0s…are you a zero, or The One?”

Posted in SQL News | Tagged: 116, Bangalore, Microsoft GTSC, PASS, SQL Clinic, SQL Saturday, SQL Server | 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.

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

Client Computer – Windows PC/Workstation/Application Server: machine name TRINITY1, Client account Domain\appadmin
SQL Server – Windows Server 2003+ running SQL Server 2005/2008/2008 R2 standalone instance: machine name NEOSQL, SQLSvc account Domain\sqladmin
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).

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.

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.

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.

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.

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.

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

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.

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

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

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

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.

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

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: access denied, bulk insert, cifs, constrained, delegation, file share, impersonation, Msg 4861, SQL, SQL Server | 34 Comments »

To SSL or not to SSL, that is the question….

Posted by Sudarshan Narasimhan on October 22, 2011

A lot of database administrators out there would have heard of SSL (Secure Sockets Layer) and the security it offers, and thus want to configure their SQL Servers to use SSL for encryption of data on the wire. Bear in mind that SSL Encryption is very different from the built-in database encryption features like TDE. SSL is used for encrypting data transmitted across the network (on the wire) between the SQL Server and a client/application. Once configured all traffic between SQL Server and a client application is encrypted which has its advantages (secure transmission) and its disadvantages (performance impact due to the encryption and extra round-trip at connection time). SSL is a powerful feature that is a given standard of most systems and will definitely feature in a list of To-Have items if any security audit or standards are enforced in your company.

In this post I am not going to talk about what the SSL/IPSec standards, but going to focus on how to get a SSL configured for your SQL Server instance. Anyone who has setup SSL for SQL Server before might have encountered a situation where the certificate from a trusted authority does not get detected in SQL Configuration Manager. I am going to elaborate how to tackle that issue as well, so read on and stay safe!

What do you need for SSL?

Certificates! You will need to purchase/provision a certificate from a certificate authority such as VeriSign or GoDaddy.com

Pre-Requisite Reading Material

Encrypting Connections to SQL Server
http://msdn.microsoft.com/en-us/library/ms189067.aspx

How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console
http://support.microsoft.com/default.aspx?scid=kb;en-us;316898

How to Enable Channel Encryption
http://blogs.msdn.com/b/sql_protocols/archive/2005/10/04/476705.aspx

SQL Server fails to start with error 17182 "TDSSNIClient initialization failed with error 0xd, status code 0x38" when server is configured to use SSL
http://support.microsoft.com/default.aspx?scid=kb;en-US;2023869

I would highly recommend you read the above mentioned articles from MSDN and only then proceed with the steps listed below. Before going further I want to highlight a few important points from the above article regarding Certificate requirements. For SQL Server to load a SSL certificate, these are the requirements.

The certificate must be in either the local computer certificate store or the current user certificate store.
The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE
The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.

Now, these are the mandatory requirements. But how do you verify these properties are satisfied before trying to load this certificate in SQL Server. Here is where the tool CERTUTIL.exe comes in very handy. (Certutil.exe comes with Windows)

I have a certificate named SUDA24322118 which I am going to check to see if the above 5 requirements are satisfied. From an elevated command prompt, run the following: certutil -v -store "my" "SUDA24322118" >certprop.txt

Open certprop.txt in Notepad and check the following values:

Validity of the certificate (Requirement #2)

Certificate is created for Server Authentication (Requirement #3)

Key Specification Property (Requirement #4)

Subject Name (Requirement #5)

The last one is the tricky one. In my case, my machine name is SUDA24322118 and hence the subject name must be the same. Once all of the above are verified, the certificate is good to be used with SQL Server.

Wild-Card Certificates

SQL Server 2008 R2 and onwards support wildcard certificates. E.g., *.ABCXYZ.com could be the CN of a certificate. This means that the SQL Server Engine (Server side components) will allow you to load a wildcard certificate for SSL. There are some caveats you need to know regarding client-side issues, so please read the accepted wildcard examples HERE.

Unable to see the certificate in the drop-down list box in SQL Server Configuration Manager

If you have religiously followed all the steps till now but still unable to see/pick the certificate to load in SQL Server, then you can follow the workaround given below to use the certificate. To reproduce this issue I created 2 certificates on the same lab machine SUDA24322118 which is in a WORKGROUP.

1. Create a test certificate: I used MAKECERT.exe which is available with Windows SDK. I have Win7 SDK installed and makecert.exe is available in "C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin\".

makecert -r -pe -n "CN= SUDA24322118" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

makecert -r -pe -n "CN= SUDA24322118.bplogix.com" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

The subject name in the 1st certificate has just the hostname of the computer whereas the second one has the FQDN. Both of them are stored in the local user store called ”my”.

2. If you open you SSCM, you see that only the 1st certificate is listed and the second one is missing.

Surprised? Don’t be. Both of them have the same properties and satisfied all the 5 certificate requirements. You can see the certificate if you open up mmc and add the snap-in for Certificates. When asked to chose the account, select “Computer Account” for the Local computer. Both of the above certificates will be present under Personal –> Certificates.

How do I get SQL Server to use the certificate with FQDN? Fortunately, there is a simple workaround for this.

3. Locate the Certificate Hash Value: We make use of certutil.exe again to get the cert hash value

certutil -store "my" "SUDA24322118"

certutil -store "my" "SUDA24322118.bplogix.com"

You can pipe the results of the above command to a text file and copy the hash value highlighted above. Remove all the blank spaces, trailing and leading spaces. For the sample above, the final hash value would be: 278e30a6ba1748bbabd360b9b8ad1d78e9104d87

4. Add the hash to the SQL Server instance Registry: Open up the registry (regedit) and navigate to the SQL Server instance hive. For e.g. a default instance of SQL Server 2008 R2 would be HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\

In the above location there would be a entry called “Certificate” which would be blank by default. Paste the hash value noted in step #3 here. This associates the certificate with this hash in the local store with this instance of SQL Server. Next time, when SQL Server starts it will load this certificate.

5. Enable Force Encryption to “Yes” using SQL Server Configuration Manager.

Note: DO NOT navigate to the Certificate tab and try to make any changes there. Just ignore the certificate tab altogether since we have directly modified the registry (which is what the tool does anyway).

6. Restart the SQL Server service. Open the latest ERRORLOG and you can see that the certificate with FQDN has been loaded successfully.

2011-10-11 22:34:10.80 Server The certificate [Cert Hash(sha1) "278E30A6BA1748BBABD360B9B8AD1D78E9104D87"] was successfully loaded for encryption.

7. Make a test connection from Management Studio (SSMS) and verify using the value of column encrypt_option in the DMV sys.dm_exec_connections is TRUE, meaning the connection is being encrypted. For the real techies out there you can use Netmon or Wireshark to check if the network packet is actually being encrypted.

That’s all folks. There is more to client-side encryption using SSL which you can read about in the above mentioned MSDN articles. Stay secure & safe. I’ll be back with more SQL Server stuff…

Posted in Security | Tagged: 17182, certificate, certutil, cluster, hash, makecert, SQL Server, SSL, TDSSNIClient, wildcard, workgroup | 15 Comments »

« Previous Entries

Next Entries »

The SQL Dude!

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

The SQL DUDE

Blog Stats

Posts by Category

Archaic Posts

Partitioned By Date()

Recent Posts

Posts by Date()

NetworkedBlogs

Feed theSQLDude

Vistor’s Location (Eye of Sauron)

FREE Email Subscription

Other SQL Blogs

Facebook me

TroubleshootingSql

SQLServerFAQ

SQL Server CSS Engineers

Vistor’s Location (Gollum’s New Eye)