Updating Central Management Server (CMS) Register Server List Automatically

August 18, 2014 Leave a comment

Microsoft released Central Management Server (CMS) with SQL Server 2008; it allowed us to have great functionality for multi-server management.  However keeping that list up-to-date can be time consuming and tedious.  For multiple reasons, primary reason because we are only DBAs, so we don’t get told anything.  We are expected to just know it right!?!

Okay, I will not get into rant about that topic.  Anyhow SQL Server instances get installed in organization (without DBA knowledge) or get uninstalled (again without DBA knowledge).  But you have the servers in your CMS registered server list; or would like to get those new servers in that list.  So how can we go about it?

There is no easy way to discover all instances in your domain; one of the few methods are:

  • SCOM
  • SCCM
  • MAP Toolkit

Probably few other, which I don’t know about.  But point is there is nothing Native to SQL Server.  So when one of my clients asked “How can we update CMS automatically?”  Only thing I could think of is SCOM.  So here it is, a PowerShell solution, that updates CMS from SCOM.

If your interested in trying it out please download a copy from Copeplex, here.  It is easy to setup, the documentation on Codeplex site includes instructions.

If you use it, please leave feedback :).

Auditing SSRS Report Execution

August 11, 2014 Leave a comment

We can use the SSRS Report Server Execution Logs (link) to review who is executing reports on the server.  These logs are saved directly in the SSRS database Report Server.  There are three views available to us to show the information on who executed the report, how long the execution took, and parameters were used.

However by default the data in the views is only kept for 60 days.   We can change that however via SSMS.

  1. Open SSMS.
  2. Connect to Reporting Instance.
  3. Right lick on Reporting Server Go To Properties.
  4. Go to Logging.
  5. Checking the Remove Log Entries Setting as per in screenshot below.
  6. Click Ok.

SSRS_SettingsI would not recommend making this number large; as it will bloat your database.  Also please don’t give access to people to run queries against this view.  Instead develop a view with filtered set and develop a report that is published against the new view.  Because the execution log contains parameter information, it might also hold sensitive information.

SQL Server Configuration Manager, WMI Error 0x80041010

August 4, 2014 Leave a comment

Just got this at my client, its normal message.  See it way to often, generally I run into this when the multiple version of SQL Servers are installed.  So decided to do a bit of research on this, namely why does it happen?  Some reasons include …

  • A SQL Server instnace on the server was uninstalled inturn it uninstalled shared compontents relarting to WMI.
  • During SQL Server installation WMI doesn’t get registered successfully.

7522_SQL_Server_Configuration_Manager_Invalid_Class_0x80041010

Its easy to fix, as per KB956013, execute following command and restart WMI Services.

mofcomp “%programfiles(x86)%\Microsoft SQL Server\number\Shared\sqlmgmproviderxpsp2up.mof”

If executed successfully you should see a successful message.  If you get an error, make sure you are running the command prompt as Administrator.

Service Principal Names for SQL Server

June 16, 2014 1 comment

Service Principal Names (SPN) get used for Kerberos authentication.  SQL Server require that all instances have SPNs configured, if not Kerberos authentication fails.  By default starting with Windows 2008 all communication between clients and SQL Server is first attempted on Kerberos.  If it fails, it falls back to NTLM.  Often if you have Kerberos issues you might also have SSPI error messages in your SQL Server errorlog.

So question can be asked, if it falls back to NTLM and I can get my work done.  Why do I care?  Simple answer is delegation.  With incorrect configured Kerberos, delegation cannot be completed and logins fail.  For example on delegation, read my post on Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ with SQL Server Reporting Services.

So what SPNs we need? Simple answer two SPN per SQL Server instance.

  • MSSQLSvc/SQLServerName[:Port] Domain\SQLServerServiceAccount
  • MSSQLSvc/SQLServerName.FQDN[:Port] Domain\SQLServerServiceAccount

Depending on your configuration, SPNs can look different.  We can manage SPNs two ways; 1) using SETSPN utility 2) using Activity Tool call EDITADIS.  Most people rely on SETSPN, as other tool is for Active Directory administrators.  And they (AD Admins) don’t like it when we mess around with their stuff; like we don’t like it when they mess around with our stuff ;-).  So lets talk about various forms of SPNs you can create.

Example #1: Default Instance, Default Port (1433), Using Domain Service Account

  • MSSQLSvc/SQLServerName Domain\SQLServerServiceAccount
  • MSSQLSvc/SQLServerName.FQDN Domain\SQLServerServiceAccount

Example #2: Default Instance, Non-Default Port, Using Domain Service Account
Example #3: Named Instance, Static Non-Default Port, Using Domain Service Account

  • MSSQLSvc/SQLServerName:Port Domain\SQLServerServiceAccount
  • MSSQLSvc/SQLServerName.FQDN:Port Domain\SQLServerServiceAccount

Example #4: Default Instance, Default Port (1433), Using Computer Account *

  • MSSQLSvc/SQLServerName Domain\ServerName$
  • MSSQLSvc/SQLServerName.FQDN Domain\ServerName$

Example #5: Default Instance, Non-Default Port, Using Computer Account *
Example #6: Named Instance, Static Non-Default Port, Using Computer Account *

  • MSSQLSvc/SQLServerName:Port Domain\ServerName$
  • MSSQLSvc/SQLServerName.FQDN:Port Domain\ServerName$

Example #7: Named Instance, Dynamic Non-Default Port, Using Domain Service Account **

  • MSSQLSvc/SQLServerName:InstanceName Domain\SQLServerServiceAccount
  • MSSQLSvc/SQLServerName.FQDN:InstanceName Domain\SQLServerServiceAccount

Example #8: Named Instance, Dynamic Non-Default Port, Using Computer Account *

  • MSSQLSvc/SQLServerName:InstanceName Domain\ServerName$
  • MSSQLSvc/SQLServerName.FQDN:InstanceName Domain\ServerName$

* An instance is consider using computer account when it is running under built in accounts, such as Network Services.
** Generally we do not recommend dynamic port as managing SPN manually can be difficult.  Also for us to use InstanceName, the SQL Server Browser service must be running.

So after reading this you are going that’s TOO MUCH TO REMEMBER!

I agree, that is why SQL Server can create and destroy this SPNs by itself without user interaction. However in order to do this, we must grant SQL Server Service Account or Computer Account permissions to Read/Write SPN Property on itself (Reference).

Now there is one scenario where we do not want to grant this permission and mange the SPN manually.  Please reference KB2443457. Copied the issue from KB for completeness.

  1. The Sqlcluster instance is active on Node A and registered the SQL SPN in domain controller A during start up..
  2. The Sqlcluster instance fails over to Node B when Node A is shutdown normally.
  3. The Sqlcluster instance deregistered its SPN from domain controller A during the shutdown process on Node A.
  4. The SPN is removed from domain controller A but the change has not yet been replicated to domain controller B.
  5. When starting up on Node B, the Sqlcluster instance tries to register the SQL SPN with domain controller B. Since, the SPN still exists Node B does not register the SPN.
  6. After some time, domain controller A replicates the deletion of the SPN (from step 3) to domain controller B as part of Active Directory replication. The end result is that no valid SPN exists for the SQL instance in the domain and hence you see connection issues to the Sqlcluster instance.

Blog post cross posted on …

  • SQLCAN (WordPress), Link.
  • SQLCAN (MSDN), Link.

Online Index Rebuild can Cause Fragmentation

June 9, 2014 Leave a comment

As Microsoft SQL Server PFE, I am always playing with SQL Server.  I generally spend most of my time on the engine side of world; so I am somewhat of a newbie when it comes to BI.  So I understand when I see something in BI world and am a bit lost.  However when I ran into this, rebuilding index online caused fragmentation.  I was some what baffled. You can try it if you wish, I tested it on SQL 2008, SQL 2008 R2, and SQL 2012.

-- Create table for testing.
CREATE TABLE Test (Col1 INT IDENTITY(1,1), Col2 CHAR(50), Col3 CHAR(50), Col4 Char(50)
CONSTRAINT pkTest PRIMARY KEY
(
        [Col1]
) WITH (ALLOW_PAGE_LOCKS  = OFF)
)

-- Insert test data.
INSERT INTO dbo.Test (Col2, Col3, Col4)
SELECT TOP 1000000 REPLICATE('A',50),REPLICATE('A',50),REPLICATE('A',50)
FROM sys.all_objects O1
CROSS JOIN sys.all_objects O2

-- Check fragmentation.
SELECT * FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null)

-- Rebuild Index online using MAXDOP
ALTER INDEX pkTest ON Test
REBUILD
WITH (Online=ON, MAXDOP=8)

-- Check fragmentation.
SELECT * FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null)

Some of you might noticed an interesting property.  ALLOW_PAGE_LOCK.

So turns out this is by design.  If ALLOW_PAGE_LOCK is turned off, we are building a index using parallelism, and building it online; it can cause fragmentation (Ref 1 & Ref 2).

Well lesson learned for future and no, it will not be last one.

Using SQL Server 2012 BPA in Windows 8.1

February 4, 2014 Leave a comment

In order to use SQL2012BPA in Windows 8.1 we require Microsoft Baseline Analyzer 2.0.  However even after installing that and SQL2012BPA we cannot do it.  Because module does not show up MBA.  Try following steps to get SQL2012BPA working on Windows 8.1.

  1. Install Microsoft Baseline Analyzer 2.0.
  2. Install SQL Server 2012 BPA.
  3. Copy the SQL2012BPA folder from C:\windows\system32\bestpractices\v1.0\Models\ to C:\ProgramData\Microsoft\Microsoft Baseline Configuration Analyzer 2\Models\
  4. Start Microsoft Baseline Analyzer 2.0.
  5. Select the SQL2012BPA module.
  6. Start analysis.
Categories: SQL 2012 Tags: ,

Cross-Domain Authentication for SQL Server

July 29, 2013 Leave a comment

Let me set the stage, we have two SQL Servers, SQLA and SQLB.  SQLA is in DomainA and SQLB is in DomainB.  I would like to access resources on DomainB.SQLB using login DomainA\UserA via SQL Server Management Studio.

There are many articles online that talk about doing this kind of work using impersonation and such.  However all those cases are for untrusted domain or non-domain.  However in my case I am working on Two-Way Restricted Forest-Level trust.  Even with this configured the SQL Servers could not communicate with each other.

I am not Active Directory guys (probably figured that out with my trouble over the years); anyhow, so I will not discuss how to set up two-way trust.  I leave that to you or your Active Directory Gurus.

Given you have two-way trust configured, everything work smoothly right? No!

Far from it, this has been one of the most frustrating issues to troubleshoot.  As there is almost no documentation I could find on the issue.  So what happened?

  • I can log on to DomainA using DomainB\UserB, no problems.
  • I can log on to DomainB using DomainA\UserA, no problems.
  • I can access File Resources on DomainB.SQLB\C$ from DomainA using DomainA\UserA no issues.
  • I can access File Resources on DomainA.SQLA\C$ from DomainB using DomainB\UserB no issues.
  • I can log in to SQL Server on DomainB.SQLB using DomainA\UserA from DomainB.
  • I can log in to SQL Server on DomainA.SQLA using DomainB\UserB from DomainA.

So all that works, what gives?  Well, when I tried logging into DomainB.SQLB using DomainA\UserA from DomainA.  Things broke down.

I started getting “Cannot Generate SSPI Context” error messages.  And if you have been as fortunate as me with Kerberos this is a fairly difficult message to troubleshoot.  But, don’t lose hope!  We will continue troubleshooting.

So naturally when the issue happened, i.e. failed login, I decided to check all the logs I can get my hands on.

  • SQL Server Error Logs
  • Event Viewer Security Logs on SQLA, SQLB

However none of the logs had anything.  So this meant two things 1) since I did not see anything in SQL Server Errorlog, the login was not being decided from the SQL engine. 2) Since I did not see any thing in Event viewer logs, the login was not being denied from Windows layer.

Okay! I am officially confused?  Right? Who else is involved in authentication process?  Aha! Domain Controller I though, but at last there is nothing on its logs.  I was running out of options fast, but with all my experience of troubleshooting SSPI issues.  I knew it had do something with SPNs.  I verified all the SPN, all the security, everything correct.  But it failed!

Then, what is going on?  So this is new one for me, I … umm … decided to look at the .NET Stack Trace data that is provided with error log.  No I am not .NET Developer, so I might be showing my limited knowledge in that field.  However … Lets look …

Procedure: GenClientContext
Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.SSPIError(String error, String procedure)
   at System.Data.SqlClient.TdsParser.SNISSPIData(Byte[] receivedBuff, UInt32 receivedLength, Byte[] sendBuff, UInt32& sendLength)
at System.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec)
at System.Data.SqlClient.SqlInternalConnectionTds.Login(Int64 timerExpire, String newPassword)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

So reading it from bottom up, all seems pretty foreign langauge to me.  Until I get to SSPIError, aha! Found what fires the error.  So one step below that must be what caused the error?  It was TdsPraser.SNISSPIData.  What the hell is SNISSPIData?  What data could it be reading?

Searching about (Bing’ing It!) seems it is a fairly common function name relating to “SSPI Context”.  So again it seems I am missing some kind of SPN maybe?

While working on the problem with a colleague, he decided to try using Force SPN with in ODBC.  Surprisingly it worked!  What the?  So I am missing an SPN?  BUT, all the required SPN are there, at least … all the required ones within single domain.

Since Forced SPN worked, we decided to create some new SPNs in each domain.

DomainA

  • MSSQLSvc/SQLA.FQDN.DomainA DomainB\SQLServiceAccount
  • MSSQLSvc/SQLA.FQDN.DomainA:Port DomainB\SQLServiceAccount

DomainB

  • MSSQLSvc/SQLB.FQDN.DomainB DomainA\SQLServiceAccount
  • MSSQLSvc/SQLB.FQDN.DomainB:Port DomainA\SQLServiceAccount

Trying connection after this everything works!  So I am calling these Cross-Domain Service Principal Names (CDSPNs).

Very interesting problem, very simple solution, just painful hard to figure out.  I am not Kerberos expert, nor do I know what all SNISSPIData does, however it seems that it was trying to verify the SPN and failing.  I wish the error message had just told me that, but ohh well.

Learning Experience!

And you know it never ends, this solution absolutely works.  However in troubleshooting this issue learned some new things about Kerberos.  Kerberos relies on DNS to be configured correctly.  I know it might be obvious to AD/Network/etc. guru out there, it was new to me.  Because 2-days after I wrote most of the post above I found cross-domain authentication using windows authentication working fine without the CDSPNs.  It turns out I scenario about because I could not resolve DomainB.SQLB from DomainA or DomainA.SQLA from DomainB.  The authentication kept failing, because Kerberos couldn’t find the FQDN for the server.  When I created the SPN, it no longer had to do the look (it seems!).

So short of it (and why I am leaving the above post still), Kerberos is not easy to configure.  Rather the issues are not easy to troubleshoot.  The original solution I found worked, but it was not the root cause of the problem.  Root cause was the DNS not configured correctly.  So if you are having issues here a rough checklist before you start pulling your hairs out.

1. Check you can ping each server by its name (i.e. Ping ServerName)

It should return ServerName.FQDN (IP), if it doesn’t we got any issue.

2. Check you can do reverse look up by its IP (i.e. PING -a xxx.xxx.xxx.xxx)

It should return ServerName.FQDN(IP), note the “-a” HAS to come become the IP to do reverse look up.

3. Have correct Service Principal Names for SQL Server Engine (two-three SPNS, 1) ServerName.FQDN Domain\ServiceAcct 2) ServerName.FQDN:Port Domain\ServiceAcct, and 3) if it’s named instance ServerName.FQDN:InstanceName Domain\ServiceAcct)

Check SPN using SETSPN -L Domain\ServerAccount, if missing add using SETSPN -A MSSQLSvc/ServerName.FQDN Domain\ServiceAcct, etc.

4. Make sure the Service Account for SQL Server is still active in active directory (i.e. password is correct, its not locked, it hasn’t expired, it still exists, etc.)

5. Make sure the Computer Account is still in AD, it has not been tombstones or deleted by AD Explorers (a.k.a. Me!).

Good Luck… if I find another items to add I will later on :).

AlwaysOn Availability Groups Troubleshooting Guide

June 26, 2013 Leave a comment

“This guide helps you get started on troubleshooting some of the common issues in AlwaysOn Availability Groups and monitoring AlwaysOn Availability Groups. It is intended to provide original content as well as a landing page of useful information that is already published elsewhere.” — MSDN Article

Contains Scenarios, Tools, and Monitoring guidelines.  Please check it out @ MSDN Article here.

SQL Server 2014 CTP1 Released!!!

June 25, 2013 Leave a comment

SQL server 2014 CTP1 has been released, check it out :).

TechNet Link.

Categories: Uncategorized Tags:
Follow

Get every new post delivered to your Inbox.

Join 231 other followers

%d bloggers like this: