SQL Permissions - The Case of the "Token-based server access violation"

I managed to create and then fix my own PEBKAC issue today and I thought I'd share the resolution in case it helps someone else out there Google-ing/Bing-ing for the solution.

The problem is failed logins and in your SQL error log you'll see entries like this:
Login failed for user 'DOMAIN\user'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: xxx.xxx.xxx.xxx]
Error: 18456, Severity: 14, State: 11.

You might see these sorts of errors when you've got domain problems or kerberos issues, but that's not the case here. You can eliminate those issues if others can log in or if the broken user/users work when you grant them sysadmin server role membership.
Since granting anyone sysadmin (even on a dev box) just isn't acceptable in my world I had to dig deeper. It turns out that the real problem was that at some point I had denied CONNECT SQL to the DOMAIN\Domain Users group. DOH!
Probably not a common thing to do but this problem would manifest itself if you had done the same thing on other groups. Maybe you have a group of accountants you don't want poking around in the db, or a group of nosy developers and you revoke CONNECT to their AD group. Then the exception arises and someone needs access but they just can't connect despite you granting them permission. Look for that deny setting and you'll be all set.

Cheers!

-A

Thoughts On SQL PASS 2011

This year I attended SQL PASS in Seattle, Washington for the first time. The experience exceeded my expectations, and my expectations were high. If you know me at all you know that I'm typically guilty of holding exceedingly high expectations from anything training related so saying that the conference exceeded my expectations really is something.

The quality of the presentations given were all very high and the knowledge demonstrated by the speakers was beyond anything I've seen before. Without a doubt SQL PASS is where you go to meet the experts. I learned so much in-depth knowledge about advanced internals like workspace memory, IO, and residuals & predicates that the time and money it took to attend will be made back in the first week that I'm back at work. I thank everyone who gave their time and talent to SQL PASS as there's no doubt it takes a huge amount of effort to pull off something that great.

Now, I'm not saying everything was perfect. The big brother/sister program was a failure from my perspective. I never met either my big brother or anyone in my "first timer" group. I was where I was supposed to be, but so were about a thousand other people and there were rooms big enough to hold maybe half of that in a usable manner. I have no idea if my big brother even showed up - according to the group leaders I found he wasn't there. I can't say I was upset about it, but if I've got any feedback to offer for areas of improvement I think that would be it - fix that whole program or just kill it off. The first timer networking session with Don Gabor was well run though, and certainly valuable.

Overall it was a fantastic week in Seattle. I met some new people, reconnected with some others, learned a lot, and for the first time ever; felt a sense of community. I think it's the sense of community which has really left me feeling motivated. I want that feeling to continue and thus I've committed to posting up a technical series!
I've decided that not a lot of people know about the magic that is MSX - TSX SQL Agent setups and thus I'll be blogging a lot about that in the near future. It's my way of saying thanks and giving back to the SQL Server community that has given so much to me.

Easy Workaround for Oracle Linked Server Problem with TIMESTAMP(6) Column

I've got a lot of SQL servers at work. And those servers link to a lot of other servers. MySQL, Oracle, SQL 2005, 2008, etc.
For the most part linked servers are frowned on from my perspective but I can't keep people from getting things done so they do get setup. Most of the time the four part naming convention works just fine although MySQL in particular seems to need OPENQUERY Oracle never has... until today.

For some reason running a query against an 11g R1 Oracle DB kept returning this:
Msg 7354, Level 16, State 1, Line 1557
The OLE DB provider "OraOLEDB.Oracle" for linked server "XYZSERVER01" supplied invalid metadata for column "CREATEDDATE". The data type is not supported.

The column type in Oracle was timestamp(6) and no matter of cast or convert would avoid the problem. Selecting without the offending column still produced the error. I was about to start looking for newer OraOLEDB drivers when I thought about the MySQL issue. I also did a little checking and found this KB:
SELECT
*
FROM OPENQUERY(XYZSERVER, 'SELECT COUNT(foobar) FROM XYZSCHEMA.XYZTABLE')

Worked great; problem solved :)

I'm also going to pose the solution to the forum post below as it seems to be related...
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/fd1cb0c8-efb5-4a94-b00d-b2aeb91dd771

MS DTC Problems With Oracle Linked Server & Windows 2008 x64 R2 Cluster (Server: Msg 7391, Level 16, State 1)

If you are getting this error (Server: Msg 7391, Level 16, State 1) trying to run a transaction using a linked Oracle server then I may have the fix you're looking for. I had to troubleshoot a problem with a new Windows 2008 Enterprise R2 x64 cluster today that was a real pain.

To get things ready normally you need to at least do the following:

• install the Oracle drivers & reboot
• set "allow in process" on the provider in SQL
• ensure the default DTC is the clustered resource
• enabled DTC for network access and set correct authentication.

Normally things would work at this point. No such luck today though! No matter what I did I couldn't get a specific stored procedure a user wrote to without firing off the "unable to enlist" error. Other DTC transactions worked just fine.

The solution was simple (but tricky for non-Oracle DBAs); I had to install the "Oracle Services for Microsoft Transaction Server 11.1.0.7.0" service and reboot.
Now, I *could* have re-written the query but since it was already working on another QA box and the user is going to be re-working the whole solution shortly I didn't see much point. Plus I wanted to know HOW or IF it could be made to work on this cluster! ;)

Hope this helps someone else out there!
-A

SQL Code: Object Location Within Data Files

I'm doing a lot of playing around with different numbers of data files and performance right now and in doing that I'm moving data around files and filegroups alot.
One of the things I find maddening is when I *think* I've moved all the data only to find that a filegroup still isn't empty. You check tables, indexes, indexed views, heaps, all look like they're in the right spot. That's why I wrote the query below. It will show exactly what data is where in your database. It's helpful for tracking down LOB_DATA that doesn't move with the rest of your table data :)

SELECT
OBJECT_NAME(object_id) AS obj_name
,p.rows
,au.type_desc
,au.used_pages
,au.data_space_id
,mf.name AS [file_name]
,mf.physical_name
,mf.state_desc
FROM sys.partitions p
JOIN sys.allocation_units au
ON p.partition_id = au.container_id
JOIN sys.master_files mf
ON au.data_space_id = mf.data_space_id
AND mf.database_id = DB_ID()
ORDER BY obj_name, au.data_space_id;

SQL Server Login Error Review & Fix for "Error: 18456, Severity: 14, State: 11."

If you've got a SQL Server machine on a domain chances are you've seen error 18456 enough times to have seen the following:

• Severity is always 14
• State 8 is a bad password (password submitted doesn't match password stored in master)
• State 9 is an invalid password (doesn't meet requirements)
• States 2 & 5 are invalid usernames
• State 40 means the logins default database isn't accessible to that login
• State 11 means the login is valid, but something else went wrong

Now, state 11 is fun. You'll get this error and then you'll probably get a call or email from someone saying they should have access but it isn't working. Often in the logs you'll see something like this:

Login failed for user 'DOMAIN\SAMAccountName'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: xxx.xxx.xxx.xxx]

This is a pretty good indicator that the users login credentials are out of date which is usually a result of a laptop user trying to access the resource with cached credentials after changing their password elsewhere.
No matter what there are two simple fixes for this that have always worked for me.

1. Have the user lock their computer and login again.
2. Have the user log off and logon again.

That's it! Those two steps will ensure that NTLM and/or Kerberos tickets/tokens are refreshed by talking to a current domain controller and the problem should go away.

NOTE: If you're seeing errors about SSPI context there is a possibility that your SQL Server service account is invalid (password changed, locked out, disabled, etc.), or your domain (or specific DC) is having issues.

Cheers!

SQL Query Optimization

Optimizing queries isn't always straight forward. Sometimes you think you've created a great index but the query optimizer just won't agree. You update stats, try maxdop hints, clear caches, and maybe even curse a little but nothing works.
You suck it up and admit you're going to have to make an exception to what you know is right and use an index/table/locking hint.

Well in doing that today I made extensive use of a few options most people may or may not be aware of. These are:
SET STATISITICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS PROFILE ON;

The output of the first two options are pretty easy to understand, but the third (which is AMAZINGLY valuable!) can be tricky.
I suggest taking a look at this blog post on the subject as it gives great detail on the use of this excellent tool.
http://blogs.msdn.com/queryoptteam/archive/2006/08/29/730521.aspx

Interesting problem with SQL 2008, VCS, and MSX-TSX Servers

I thought I was going nuts today when I couldn't get a TSX server to use it's MSX server. Enrollment went through without a hitch, but the TSX would never get any jobs or even probe (although the MSX said it was probed).
After a LOT of digging I eventually found the problem. The TSX is actually a Veritas/Symantec cluster with a virtual name but when the MSX was calling things like msdb.dbo.sp_sqlagent_probe_msx it would use the hostname of the cluster node running the SQL service for the value of @server_name. This was weird. Both @@SERVERNAME and SERVERPROPERTY('SERVERNAME') returned the virtual name and the TSX enrolled as the virtual name but it was always calling the damn hostname!
The fix for this was quite obscure.
I eventually happened upon a key
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT]
"Environment"

And this key was missing for this cluster, yet there for every other SQL server I've got. I took a chance and put in the data for the key that was present under the service for this instance and bounced the service. BAMN! Just like that it downloaded all it's jobs and the MSX started issuing sp_sqlagent_probe_msx calls using virtual name!

I have no idea why this happened (SQL 2008 seems 'flaky' with permissions like this) but at least the fix was easy.
Keep an eye open for this if you're running a similar setup!

HEAP Fragementation in SQL Server 2005

I happened upon an interesting problem at work today. There's an application my company purchased that is used for project management in one way or the other. Each night a job runs that does various things including synchronizing variables like permissions and user names with Active Directory. The vendor supplied the script that does this and it's terrible. It involves dumping a lot of data into a work table, then pulling that data out piece by piece, converting it to XML, comparing the XML to some other XML, and then re-inserting into the database. Yikes.
Anyways, the performance of this sync job is fairly constant until a certain point and then it just blows up and quadruples the run time which affects other processes.
Previously this was solved when I noted the complex system we use to defrag our databases had a bug in it. I fixed the bug, defragmented things and everything went back to normal.
This time however, there is no bug and the tables that play a role didn't show any sort of fragmentation at all. In QA I just went ahead and defragmented everything with the "quick & dirty" rebuild script

 EXEC sp_msforeachtable 'ALTER INDEX ALL ON ? REBUILD' 

Never, ever use that on a production database :p
A test showed that the sync was indeed working properly again. Great. So all we need to do is ensure that the database has essentially zero fragmentation on all tables and indexed views to get this to work. I'd also take a pet unicorn while we're wishing.
So I did a little more digging around and I noticed that our automated index rebuilding script didn't consider index_id = 0 (HEAP) which meant I wasn't viewing any data for that while reviewing fragmentation levels. I coded up a very quick fragmentation check query

SELECT object_name(object_id)
 ,index_id
 ,partition_number
 ,page_count
 ,avg_fragmentation_in_percent
 ,index_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(N'--dbname--'), NULL, NULL, NULL , 'LIMITED')
ORDER BY page_count DESC, avg_fragmentation_in_percent DESC;

What I found shocked me. There were 10+ large heaps with 99.5%+ fragmentation!!
I immediately started to dig in and see why these tables didn't have clustered indexes and you know what I found? No reason. No reason at all. There were already non-clustered and unique indexes on every table I found. For some reason the developer/vendor just never clued in that over time they would create massive fragmentation on these tables and not give us poor DBAs a chance to fix it. My assumption is that this was developed way back in the SQL 6 or 7 days where I *think* they used linked-heap table structure... don't quote me on that though as I've never touched anything older than 2000 (and oh man do I not miss 2000!)

If you're not sure how I fixed things yet I'll tell you. I converted a UQ index on each table to a clustered index (using a best guess approach) which rebuilt all the others. Fragmentation is under 1% and I'll be able to monitor the fragmentation now to see if I need to do anything else with these tables.

All this work because a vendor didn't bother to properly design their database. Shocking, eh? ;)