Public Not Granted Server Permission

I have been setting up policies on my 2008 servers. I thought a good place to start would be with the suggested policies Microsoft provides with their samples. You can download the sample databases from codeplex.

I can came across a sample policy named “Public Not Granted Server Permission”, now this sounds like something a person would want to implement. I imported the policy and ran it on my test system and discovered my system failed, but I was not really sure why. The policy explains that the server role public has been granted server level permissions. OK, but what does that mean? What server level permissions? This policy does not provide an option to force compliance, so the dba must correct the issue manually, but how and if you correct what permissions will be revoked?

I started by trying to investigate the @PublicServerRoleIsGrantedPermissions facet, but that provided no greater information. Then I found this great blog post by Randy Dyess who delves into the guts of the public role. He provides a script to identify the permissions that the public role has been granted. Using this script I determined that a fresh install of SQL Server by default grants the public role the following permissions:

Name	Permission	Class	Securable	State
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

Of these permissions only VIEW ANY DATABASE is the only server lever permission. I am familiar with this permission. If you DENY VIEW ANY DATABASE to PUBLIC a user will only be able to see the master, msdb and databases in which they own. This would prevent a user from seeing the other databases on the server listed in management studio. The problem with this is that I don’t make any user dbo. If a developer wanted to connect via Studio Manager they would not see database which they have been granted permission within. They can still connect to the database, but can’t see it in Management Studio which kind of defeats the point.

For my testing I went ahead and issued the following:

DENY VIEW ANY DATABASE to PUBLIC.

I re-evaluated the policy and it still failed. So on the advice of Brian Kelley, I issued the denied all connect permissions to the Endpoints.

DENY CONNECT ON ENDPOINT::[TSQL Named Pipes] to public

DENY CONNECT ON ENDPOINT::[TSQL Local Machine] to public

DENY CONNECT ON ENDPOINT::[TSQL Default TCP] to public

DENY CONNECT ON ENDPOINT::[TSQL Default VIA] to public

This worked and the policy passed. However, this is not a policy that I will be implementing.

Speaking at Louisville PASS Chapter

On Thursday June 11th from noon to 1pm I will be speaking at the Louisville PASS chapter meeting. The meeting is being held at Kindred Healthcare, 680 S. Fourth Street. The topic is "Defensive SQL Server Security." I believe that Idera will be providing Pizza.

Set Number of Error Logs via code

In my ongoing effort to automate everything. I wanted to have a script that would change the default number of error logs. After some looking around in help and on the internet, I realized I was going to have come up with a solution on my own. I was complaining to Ryan about having to do some real work, when he suggested that I just run a trace when I make the change via Management Studio.

Here is the result:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 10

I am sure using this is not recommended, but then MS should have provided me a better method to accomplish this.

32-bit to 64-bit SQL 2008 Upgrades

I am not sure if it is a symptom of my advanced age, but I am forgetting more and more. I know that you can't do an inplace upgrade from a 32-bit version of SQL 2005 to a 64-bit version, but I did indeed forget. I am running into this issue more frequently on SQL 2008 upgrades than I did with previous version, becuase SQL 2008 offers Workgroup Edition 64-bit for the first time. Many of the customers running Workgroup Edition SQL 2005 in a WOW configuration are looking to upgrade.

The only workaround that I knowis to:

• Script out logins, jobs, and any objects that are stored in MSDB and Master
• Detach user databases
• Uninstall 32-bit version of SQL Server
• Reboot Server, just to be sure
• Install 64-bit version of SQL Server
• Create logins, jobs and other server wide objects
• Re-attach user databases

New MVP

Congratulations to my friend Chris Shaw who was finally award an MVP for SQL Server. Chris has been working for years supporting the SQL Server Community and I am very happy for him.

ps. Chris is a non-douchebag MVP

Getting Back to Basics

Johnny has been on vacation this week ( I hope he is having a good time, because he will never get to go on another one ever again, he does too much around here) and I have been covering much of his responbilities. Configuring SQL Server instances, fixing failed jobs, answering support questions, etc, etc. I will be honest, I do not much of this work any more. While some of it is tedious, I am getting the chance to remember why I like being a DBA so much. It is the daily problem solving. Some jobs you can work on one problem for years, while that is fine for some, I prefer many small victories. A problem arises and I can fix it move on to another problem.

When I worked at a big corporation, problems rarely happened. Which is a good thing, but it is boring. Now I work for a hosting company and I have the chance to work with a lot on database novices and new DBAs. Everyday I am challenged to fix something that has gone wrong for someone else. Personally, I get a lot of satisfaction of teaching others new things about database and solving their problems.

Disaster Recovery Horror Stories

Brent Ozar is hosting a webcast today about disaster recovery horror stories and I thought back to my “favorite” horror story and how much I have learned since then. I have been working with SQL Server since 1997, but I was not a real dba until I took a job at large insurance company in 2000. At that time, a lot of large companies only trusted SQL Server to be the back end to their websites, but did not trust it to run their actual business.

This company was like many their website kind of grew up without any real structure or plan. However, the company realized that they needed add structure to their site and I was the dba on site to help roll out the changes to the database. The updates were expected to take 3 hours to complete and we got started at 10 AM. Now I was not given these updates before hand, so I did not know that updates used cursors instead of set based queries to update hundreds of tables with millions of rows.

Let me stop here and give you some background, at this time had two very large dogs. A super friendly German Shepherd mix and a pit bull / mastiff mix named Truck who weighed about 100 pounds and was very protective of me and our house. I was obsessed with these dogs, they were my life. Truck passed away 2 years ago and I still talk about him constantly.

A 3 hour update took closer to 9 hours, but then the problems started. The website no longer served pages, every page was giving a timeout errors. Of course, today I would immediately know what to do and what the problem was, but then was a different story. A conference call was created with every vp at the company on the line, web developers, hardware guys, network guys and me and the lead dba. The lead dba had less experience then I did. Everyone was off checking their own thing and no one could figure out what was happening. By about 10 hours in, I was in a panic, my dogs needed to go out and be feed. I was new to the city and did not have any friends or family to go check on my boys. The VP of my division would not let me leave, but because I was so upset he said that he would go let my dogs out. I told him that he did not understand that Truck was a very scary dog. I continued to work while the VP went to let my dogs out.

I finally got the bright idea to run a trace on the database and I noticed that most queries took 10 ms to execute but this one took 10 minutes to run and it seemed to be called all the time. Duh! I was young and stupid. This was the early days before change control made their way to sql server, so I just added a couple of indexes to this table and poof, the site loaded. The site came up, but that was about it. It was performance nightmare. About this time, is when the VP called from outside of my house to say he was not going in, but found a neighbor kid who did yard for me to brave it. Truck knew the neighbor kid and let him in, but hated that VP.

So I worked for 16 hours running traces, adding indexes until the site worked. After that I was the human performance tuning wizard.

The post script to this story is the actual worse disaster I had, but is far from my favorite story. I had scheduled to apply SP2 on one of our large SQL 2005 one Friday night. We had tested SP2 and applied on about 15 servers prior to this install I did not anticipate any issues. Because we have international customers our maintenance window is Saturday morning at 2 AM, a time I hate. Sleepy DBAs make bad decisions, but it is part of the job. Before I started I took a full system backup, and then ran the SP2 installer. Some of you may have encountered the issue with SP2 where it failed due to a previous installation. So I went out found the Windows installation cleanup tool and removed anything I thought may be the problem. I reran the SP2 installation and it installed without issue. I thought I was finished and just rebooted the server. But the server would not come back up, the master database was corrupt. How , Why, I am not sure. So I try to restore from my backup, but I am unable to because I was getting an error that the backup was not readable. I knew then that I was in for a long night.

Another back story, Truck, my beloved dog had cancer. He was undergoing chemo and I had hoped that he was doing better. I knew there would be no cure, but still I hoped. The day of the upgrade I noticed that he was a little lethargic, but that sometimes happened after chemo.

Truthfully, I don’t remember exactly what I did, but I was able to get the server backup. I think I restored the master database backup on another server as regular database and then made a backup of the restored database. This new backup worked, but then I found out there were problems with MSDB. At this point, I had been up for 24 hours and was exhausted. So I made the call to go home get some sleep and then deal with the MSDB problem the next day.

The next day, Truck was still lethargic, but truthfully I was so consumed with my broken server that I did not pay much attention. I continued troubleshooting the msdb issue, but made the decision to scrap the entire install and reinstall SQL Server on new hardware, it was just screwed up. I scheduled this to happen Sunday night at 10PM.

Thankfully, the re-build went off without incident and the server was backup and running. The next morning, a new DBA (Ryan) was starting so I would have help and not have to deal with this kind of thing alone in the future. I went home and discovered that Truck had taken a turn for the worse. I rushed him to the emergency vet and stayed most of the night up with him. I called our CTO and asked him to welcome Ryan and get him settled, because I had to take Truck to cancer vet first thing in the morning.

The vet told me that he would need to run some tests on Truck and that I should go into work. So I went to work welcome Ryan and started to deal with the fallout of the weekend. A few hours later the vet called and told me that Truck was bleeding internally and that it was time. I left work to say goodbye to Truck and then went home where I planned to stay for the next few days mourning the worldest greatest dog. However, the life of dba is not always convenient.

On the day my beloved Truck died, I had to go back into work to help a customer who had encrypted all their data using the server master key, which we do not support and do not backup. When the server was rebuilt, a new master key was generated. I am no encryption expert and I was in no mood to deal with this situation, but Ryan had been on the job less than 6 hours and I was the only dba, so I called up Microsoft to help me try to recover the master key from the old server which was not easy. On the worse day of my relatively young life, I spent on the phone with pss.

When things wrong, it is rarely convenient.

The MVP Summit

I love twitter. I enjoy reading the tweets of my follow SQL Server professional expert and the regular guys. I learn a lot and enjoy commisserating with my fellow professionals on the pains of the dba profession. However, this week twitter is annoying me. Microsoft awards many of the top SQL Server Experts with the title of MVP, which entitles them with all sorts of benefits and perks. I will admit that I would like to be MVP and have access to all sorts of information and resources, but I do pretty well now with the resources us regular folks have access. This week is the MVP summit, where all the MVP's go up Redmond and learn new secret things before the rest of us. I knew this event happened in the past, but now thanks toTwitter it is being thrown in my face. It is kind of a douchebag move to twitter "I am learning all this really cool stuff that I can't share with you because you aren't an mvp"

Performance Advisor Saves the Day

One of the most annoying questions i get is "My application went down last Tuesday aroun 9 AM, what happened?" I often want to reply "How should I know? How about calling me when it happens." But I don't, because I am not a complete jerk. Now I can answer that question because I have Performance Adivsor.

What happens is that customer will notice a problem with their application and check their error logs, the error log will give the dreaded "SQL Server Timeout Error" and logically the customer will think there was a problem with the SQL Server and contact me. In reality, the problem is almost never a SQL Server problem, but an application issue. Proving this another matter.

First of all the SQL Server does not timeout, the client connecting to the SQL Server is actually timing out, as I do not set a maximum timeout on the sql server. In the past, I was limited to error logs and the default logs to try to determine what was happening on the server at the time of their time out. Not much help. If there was a major issue it will appear there, but if there was a major issue I would already know about it. I could not tell them what was happening specifically with their database, perhaps their was a lock that caused the query to take longer than normal?

When SQL Sentry told me about their new product Performance Advisor, I was very excited. I bugged them for months to get my hands on it. Now the product can do all sort of cool real-time monitoring, but in reality I can't keep up with that as I just have too many servers. I use it to tell me about what happended in the past and about trends. I love being able to provide our customers with real information to help get to the root of the timeout. I can say at that time of your time out the server was using X% of CPU and your database executed the following statements and it took X ms to complete. It makes me look better and I can actually answer the what happened question.

Truthfully, I don't find many third party products useful, but this one I do like.