A couple days ago I was playing around with some Event Notifications and the Certificate that I’d created for them on my development machine.  Low and behold I’d made a classic mistake and forgotten to write down the password for the certificate.  Thus I was left without a critical component necessary for signing my procedures.  Since this really wouldn’t do, I opted to drop the certificate from the server.

Now this shouldn’t be such a problem a siimple DROP CERTIFICATE statement and I should be good to go.  The statement I used look just like this:

 IF EXISTS(SELECT * FROM sys.certificates WHERE name = ‘MyCertificate’)
    DROP CERTIFICATE [MyCertificate]
GO

Yup, Didn’t Work

There wouldn’t be much of a point to this if this worked as intended.  So here’s the rub, the statement ended up generating the following error:

Msg 15352, Level 16, State 1, Line 1
The certificate cannot be dropped because one or more entities are either signed or encrypted using it.

Backing up a bit, the reason that I created this certificate is that I’ve been using it to sign stored procedures.  I was doing that so that the procedure can execute under that a login that has the permissions I want rather that just opening a big security hole in my development machine.

What’s Signed By Your Certificate

To find out what’s signed by the certificate, you can use the sys.certificates and the sys.crypt_properties system views.  The query I use for this is below:

SELECT OBJECT_SCHEMA_NAME(co.major_id) + '.' + OBJECT_NAME(co.major_id)
FROM sys.certificates c
    INNER JOIN sys.crypt_properties co ON c.thumbprint = co.thumbprint
WHERE co.crypt_type_desc = 'SIGNATURE BY CERTIFICATE'
AND c.name = 'MyCertificate'

Hopefully, you won’t forget your certificate password like I did.  But you may need to remove a certificate from one of your systems some time and hopefully this get you passed that irritating error above.

It’s a very good question. One that might not seem to insidious. Nothing that should be able to bring down the system and cause failures. Or will it?

I’ve been to a number of clients and done it myself before where I start to check out a stored procedure with some performance issues and sitting all pretty at the bottom is a GRANT EXEC statement. When I script out the stored procedure I get something similar to the following:

CREATE PROCEDURE dbo.FooGetTableA

    (

    @Parameter varchar(4)

    )

AS

SELECT Column1 

FROM dbo.TableA

WHERE Column2 = @Parameter

GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole

GO

But if you look carefully, there is something missing, or one could say included that shouldn’t be.  Look again if you don’t see it. It’s hidden in plain sight.  The permissions for the procedure are included in the body of the stored procedure.  When the procedure was written, someone thought ahead to add permissions to the script but forgot the GO statement between the stored procedure

In a better world this script would have looked like this:

CREATE PROCEDURE dbo.FooGetTableA

    (

    @Parameter varchar(4)

    )

AS

SELECT Column1 

FROM dbo.TableA

WHERE Column2 = @Parameter

GO

GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole

GO

It’s Just a Permission Statement

Who cares, right?  So you are assigning some permissions every time that procedure executes.  What harm could possibly come of it.  I’ve seen this so many times and usually it’s one of things I’ll point out and say, “oops, you should take care of that”.  When I should be saying, “yeah, fellas.  You’ve got a time bomb there waiting for your business to take off.”

And the time bomb is deadlocks.  Completely preventable deadlocks.

If you have procedures that grant themselves permissions, then as the volume of activity in your database increases you may start to see deadlock graphs similar to the following:

deadlock-list deadlock victim=process30108bac8  process-list   process id=processec55dd68 taskpriority=0 logused=0 waitresource=METADATA: database_id = 10 PERMISSIONS(class = 1, major_id = 219199881) waittime=15000 ownerId=746424569 transactionname=Load Permission Object Cache lasttranstarted=2009-10-22T23:06:59.287 XDES=0x3712a8e98 lockMode=Sch-S schedulerid=1 kpid=5832 status=suspended spid=157 sbid=2 ecid=0 priority=0 transcount=1 lastbatchstarted=2009-10-22T23:06:59.287 lastbatchcompleted=2009-10-22T23:06:59.280 clientapp=.Net SqlClient Data Provider hostname=PRDWB0111 hostpid=5640 loginname=portaluser isolationlevel=serializable (4) xactid=746424394 currentdb=10 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056    executionStack     frame procname=AdventureWorks2008.dbo.FooGetTableA line=1 sqlhandle=0x03000a0089b9100d0e527800669c00000100000000000000CREATE PROCEDURE dbo.FooGetTableA    (    @Parameter varchar(4)    )AS

SELECT Column1 FROM dbo.TableAWHERE Column2 = @Parameter

GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole    inputbufProc [Database Id = 10 Object Id = 219199881]       process id=process30108bac8 taskpriority=0 logused=0 waitresource=METADATA: database_id = 10 PERMISSIONS(class = 1, major_id = 1746157316) waittime=2125 ownerId=746479249 transactionname=Load Permission Object Cache lasttranstarted=2009-10-22T23:07:12.180 XDES=0x3786c61c8 lockMode=Sch-S schedulerid=3 kpid=4048 status=suspended spid=69 sbid=2 ecid=0 priority=0 transcount=1 lastbatchstarted=2009-10-22T23:07:12.180 lastbatchcompleted=2009-10-22T23:07:12.167 clientapp=.Net SqlClient Data Provider hostname=AMBER hostpid=568 loginname=portaluser isolationlevel=serializable (4) xactid=746372404 currentdb=10 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056    executionStack     frame procname=AdventureWorks2008.dbo.FooGetTableB line=1 sqlhandle=0x03000a00043f146882564201a09b00000100000000000000CREATE PROCEDURE dbo.FooGetTableB    (    @Parameter varchar(4)    )AS

SELECT Column1 FROM dbo.TableBWHERE Column2 = @Parameter

GRANT EXEC ON dbo.FooGetTableB TO ApplicationRole    inputbufProc [Database Id = 10 Object Id = 1746157316]      resource-list   metadatalock subresource=PERMISSIONS classid=class = 1, major_id = 219199881 dbid=10 id=lock4153ec880 mode=Sch-M    owner-list     owner id=process30108bac8 mode=Sch-M    waiter-list     waiter id=processec55dd68 mode=Sch-S requestType=wait   metadatalock subresource=PERMISSIONS classid=class = 1, major_id = 1746157316 dbid=10 id=lock415451780 mode=Sch-M    owner-list     owner id=processec55dd68 mode=Sch-M    waiter-list     waiter id=process30108bac8 mode=Sch-S requestType=wait

Breaking It Down

When I first started looking at these there are a few things I noted right away:

1. The procedures were access completely different tables with no common objects between them.  In the sample above there is TableA and TableB and no relationship.
2. Looking at each of the processes in the deadlock both of them have the following attributes
1. waitresource=METADATA: database_id = 10 PERMISSIONS
2. transactionname=Load Permission Object Cache

So nothing in common and a deadlock on a metadata resource for permissions.  This made me start to re-think how the two procedures were related.  With a metadata resource wait, there seems to be an issue above the data in the table.  Since both procedures point to the Load Permission Object Cache, maybe there is an issue there.

If you take a look, each of the procedures has a GRANT EXEC permission statement in it.  This is the area of commonality and where the two executions deadlocked.  Removing the GRANT EXEC permissions statements stop this deadlock from occurring.

After going through and removing these permission statements from a number of procedures that had this issue, all of the deadlocks with these types of issues disappeared.  And it is smooth sailing once again.

Cautionary Tale

Hopefully this is a scenario that only I’ve run into.  But if it’s not then this should serve as a reminder that little details that seem like a little non-issue, could be the crack that breaks the damn when there’s enough water behind it.  The thing that gets you on this issue is that it isn’t until execution start to really grow before it pops out and it will only hit when you’re the busiest.  This is something I’ll be keeping an eye out for in the future and I’d recommend the same for others as well.

I saw this post a few weeks back and got around to downloading it myself tonight.  If you don’t know what it is.  It’s a free SQL Server security book.

Best of both Worlds

There are two things I like about this e-book.  The first, it is free.  And the second, it’s a book on security which there will never be enough of.  Sadly, security is one of those areas that seem to be overlooked until it is often too late.

The topics and speakers for this months PASSMN meeting have been announced…

SSIS – Team Development, Deployment and Configuration & Securing and Troubleshooting Service Broker

May 19, 2009
3:00 PM – 5:15 PM

SSIS – Team Development, Deployment and Configuration
Speaker: Dan English, Magenic

SQL Server Integration Services (SSIS) provides enterprise-class scalability, advanced data-integration architecture, and high-performance processing. Many enterprise environments are developing centralized services and standards to support their SQL Server Integration Services platform. During this session you will learn considerations and solutions for team development and how to leverage the power of package configurations for deploying packages to multiple environments.

Dan English - Dan is a Principal Consultant with Magenic and has been developing with Microsoft technologies for over 12 years and has over 5 years experience with Data Warehousing and Business Intelligence. He has been working with SQL Server since version 6.5 and now with 2008 looking towards the Kilimanjaro release. Dan has screencasts of SQL Server 2008 and PerformancePoint Server on YouTube and Soapbox ( keyword search – Magenic) and is an avid blogger (http://denglishbi.spaces.live.com). Dan is fully certified with MS SQL Server 2005 and 2008 Business Intelligence. He enjoys keeping in contact with the community at large responding to forum postings on the Microsoft forums and SQL Server Central areas. Dan is also part of the PASSMN 2009 Executive Board.

Securing and Troubleshooting Service Broker
Speaker: Eric Strom, RELS

Tired of reading “Hello World” articles about Service Broker? Looking for more information but not finding good resources on securing and troubleshooting Service Broker applications? In this presentation, I will discuss some good security practices and share some lessons I learned while implementing and troubleshooting a medium-sized Service Broker application. Expect to learn about securing and troubleshooting Service Broker. A basic understanding of the Service Broker architecture is helpful.

Eric Strom is a Senior Database Administrator at the RELS Companies and is a member of the PASSMN 2009 Executive Board. He has been a SQL Server DBA since 2001 and specializes in performance tuning. Eric studied database theory at the University of Minnesota to earn a B.S. in Computer Science. He loves exchanging ideas with peers and is always looking for a good discussion.

I keep hearing this story and similar variations…

”On a dark and stormy night, I developed a SQL Server Integration Services (SSIS) package.  It worked wonderfully on my desktop development environment.  All the files were properly accessed and the data was processed in a magnificent matter.”

”But suddenly I deployed the SSIS package to production environment, or sometimes even the test environment first.  And BAM! the SQL Agent job I created to execute the package would fail.”

”I laughed and cried and checked all of the settings and permissions and everything is identical to the development environment.  I just don’t know what is going wrong.  Help me Obi-Won, you’re my only hope.”

The first thing I do any time I hear this story is to tell the developer that I think it’s really weird to actually say “open parenthesis” and “close parenthesis”.  But, right after that I check the execution log for the package.  The execution log will usually have all this verbiage around it not being able to this, that, or the other thing with some external resource that the SSIS package is referencing.

But the developer had stated that everything is identical to the development environment but this, of course, doesn’t mean that the SQL Agent account is actually the same between the two environments.  And this is where the problem often lies.  In the development environment the package was executed by a power user, the developer, that could access anything the developer wanted.  In nice and secure SQL environments, the SQL Agent service account will have minimal permissions within SQL Server and no permissions outside of the server that it doesn’t require.

New package.  New requirements.  New network permissions.  New proxy.

As I mentioned, the account executing the SSIS package has different between the environments.  What if I could run the SSIS package on the production environment with the SAME permissions I ran the package with in the development environment.  SQL Server 2005 and 2008 allows for this through the use of CREDENTIALS.  Books Online defines CREDENTIALS as such:

A credential is a record that contains the authentication information (credentials) required to connect to a resource outside SQL Server. This information is used internally by SQL Server. Most credentials contain a Windows user name and password.

The information stored in a credential enables a user who has connected to SQL Server by way of SQL Server Authentication to access resources outside the server instance. When the external resource is Windows, the user is authenticated as the Windows user specified in the credential. A single credential can be mapped to multiple SQL Server logins. However, a SQL Server login can be mapped to only one credential.

To execute a SQL Agent job under the developers account the following four things need to be done:

1. Create the CREDENTIAL using CREATE CREDENTIAL.

-- Create a credential with the account Domain\User and its password

USE [master]

GO

CREATE CREDENTIAL MyCredential WITH IDENTITY = N'Domain\User', SECRET = N'Password'

GO

2. Create a proxy that references the CREDENTIAL using msdb.dbo.sp_add_proxy.

-- Create a proxy and assign the credential to it

USE [msdb]

GO

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'MySSISProxy',@credential_name=N'MyCredential',@enabled=1

GO

3. Grant the proxy access to the SSIS subsystem using msdb.dbo.sp_grant_proxy_to_subsystem.

-- Grant proxy access to the 'SSIS package execution' subsystem, aka #11

USE [msdb]

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'MySSISProxy', @subsystem_id=11

GO

4. Assign the Run As value for the SQL Agent Job Step to the proxy.

Often by going through these steps I’m able to show that the package that runs in the developer’s development environment under his login will also run in the production environment under his login.   Now identical really means identical and through either adding permissions to the SQL Agent service account or, even better, creating a CREDENTIAL with the appropriate permissions that package can be executed as intended.

Nice and Secure Environments

I should note that using the developer’s login is a temporary troubleshooting technique.  The developer’s login should not be used on an ongoing basis for the SQL Agent job.  This will open up a whole host of issues such as password resets and proxy accounts that stop working with developers logins are disabled.

And as I mentioned above, some people like nice and secure SQL Agent environments.  I am a big fan of those as well.  CREDENTIALS are a great tool that should be used to keep SQL Agent secure.  By limiting the scope of the SQL Agent service accounts, SQL Agent can be prevented from accessing resources and data that it shouldn’t normally access.  I’m not going to get too much into security best practices around this other than to say if restricting SQL Agent permissions isn’t a current consideration you should read the SQL Server 2005 Security Best Practices – Operational and Administrative Tasks white paper.

Tagged

I’ve been tagged…  Jason Massie tagged me this week and I’ll be responding to it in Monday’s blog.

I’ve never been a fan of the SQL Server 2005 Integration Services task for transferring logins between servers.  It seems that I always misconfigure it or something gets missed.  And when I just want to move a single login its more effort to setup the task than it is worth.  What I’m really saying here is I’m not a fan of the task and I don’t plan to get "gooder" at using it.

Especially when there is a much better alternative… scipting the login with the password.  The link has a script that creates a couple stored procedures that, when executed, provides either a single or all logins with their properties.

The output script looks something like this:

– Login: ExistingUser
CREATE LOGIN [ExistingUser] 
WITH PASSWORD = 0×0100DE1894107F53A6ABF5436BA4F2A6BFFC0C51C156760E70F2 HASHED, 
SID = 0xDA25EBDBF88087D98B65D94B1DF3155B, 
DEFAULT_DATABASE = [master], 
CHECK_POLICY = OFF, 
CHECK_EXPIRATION = OFF

Very useful indeed…

I’ve not done recommended changing the sa account name at a client for quite a while.  Since the account can be disabled there isn’t any good reason to rename it that I’ve been able to come up with.

But if you do and plan to upgrade to SQL Server 2008, beware of the following…

http://blogs.msdn.com/psssql/archive/2008/09/10/upgrade-for-sql-server-2008-can-fail-if-you-have-renamed-the-sa-account.aspx