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.
- Install Microsoft Baseline Analyzer 2.0.
- Install SQL Server 2012 BPA.
- Copy the SQL2012BPA folder from C:\windows\system32\bestpractices\v1.0\Models\ to C:\ProgramData\Microsoft\Microsoft Baseline Configuration Analyzer 2\Models\
- Start Microsoft Baseline Analyzer 2.0.
- Select the SQL2012BPA module.
- Start analysis.
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 …
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 Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
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.
- MSSQLSvc/SQLA.FQDN.DomainA DomainB\SQLServiceAccount
- MSSQLSvc/SQLA.FQDN.DomainA:Port DomainB\SQLServiceAccount
- 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.
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 :).
Before I talk about this error message, I want to apologize to followers and students I had promised this post a while back. Sorry for delay, I will try to be more prompt on my future posts.
Anyhow, so how many people have seen that exact message? “Login failed … ‘NT AUTHORITY\ANONYMOUS LOGON’”? What does this message even mean? And why do I have to deal with it?
In my experience I have seen this message in three or four difference cases. I think in all cases the issue is similar, so I hope this post helps your troubleshoot and fix this issue within your environment.
When running reports on SQL Server Reporting Services (SSRS) for some reason I keep getting login failed with ‘NT AUTHORITY\ANONYMOUS LOGON’. I am not sure what is going on, the connection string for report is set to “Windows Integrated Security” like in image below. I have access to the server, I confirmed my login “Domain\username” has full rights to the SQL Instance and database, hell I am “sysadmin”; there should be no reason to get this error message.
So what is going on?
Well one can say it is an issue with your connection string? That is you have it set to “Windows integrated security” that is why you are getting this. So people often either a) hard code SQL Server login credentials or b) a service account credentials within the connection string. All-in-all, I don’t have problem with that however it makes it hard to control what an individual can execute or what they have permission to. Windows integrated security is a good approach.
That said what is the issue then?
Problem is simple, it is an issue with delegation. Windows 2008+ defaults to Kerberos authentication model. In which when you log into SSRS, it uses your credentials to authenticate you. However when you run the report, for SSRS to authenticate you to the SQL Server instance in question, we must have proper delegation setup with in active directory. For that we have to do following steps to resolve this problem…
Check to see if you have SSRS configured correctly
- RDP to SSRS server.
- Go to Reporting Services Configuration Manager.
- Go to Service Account.
- Confirm that you have Service Account set to a Domain Account and NOT Network Services (Default).
- Check for Service Principal Name using following command:”SETSPN -L Domain\ServiceAccountName” (this is your SSRS service account).
- You should have one SPN “http/servername.FQDN”. If you are using non-default port (other then 80); than it should be “http/servername.FQDN:PORT”.
- If you are missing the SPN please add it using the following command:”SETSPN -A http/servername.FQDN:Port Domain\ServiceAccountName”.
- Now go to your Active Directory administrator and ask them to give the SSRS service account we created SPN for in step #7, Delegation rights under Delegation Tab, “Trust this user for delegation to any service (Kerberos only)” as in screenshot below.
- Restart SSRS.
If you try to run your report it should work now :). If you guys have any other issues please let me know.
- If you change service account in your SSRS, please back up the encryption key before changing.
- If you are using single service account for everything (not best practice), consider setting “Trust this user for delegation to specific services only”.
“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 has been released, check it out :).
SQL Server Build: 11.0.3368 KB: 2833645
Lots of fixes are in this CU, in addition at least one KB talking about few new features. One related to syspreping analysis services, integration services, etc.
SQL Build: 11.0.2405 KB: 2823247
Quite a few fixes, similar to the ones in SQL 2008 R2 SP1 CU12 and SQL 2008 R2 SP2 CU6. Some issues specific to SQL 2012 are related to SSAS, fixing performance issues with systems with more then 32-logical processors, access violation issues with FILESTREAM-enabled but data is offline. FULL JOIN, DMV, Log Shipping, etc.
SQL Build: 10.50.4279 KB: 2830140
Many fixes are same as those released for SP1 CU12. But there are some additional fixes in this CU for PowerPivot, SSRS, Cursors, Encryption, one for Database Mirroring.
Build: 10.50.2874 KB: 2828727
Mostly engine fixes, few fixes for SSAS. Some interesting fixes around SQL Server freezing on shutdown, Assertion check failed/access volition for partitioning, index size issues with RCSI, data corruption.
How many times have we heard that SQL Server is running slow from end-user, but can’t track down why? Or what happened? In this post I hope to point out some of the things you can consider. I’ll approach this scenario two ways, 1) SQL Server is running slow due to processor is pinned 2) SQL Server is running slow but processor is not pinned.
SQL Server is running slow due to processor is pinned
What are some of the things that require CPU in SQL Server? When dealing with processor related issue, we need to know what might cause CPU utilization and what kind of utilization we have (privileged or user)? Before we start troubleshooting any kind of issue on the server, please ask the first obvious question? Is it really sqlservr.exe (SQL Engine) causing the issue? In CPUs case it is pretty easy, we can look at task manager. However if we wanted to look at the Windows Performance Monitor (perfmon), then we might want to look at least three counters; Processor\% Processor Time (_Total), Process\% User Time (sqlservr), and Process\% Privileged Time (sqlservr).
An interesting difference between Processor and Process counter is, Processor counter is an average of all processors visible to the operating system. Where as Process counter is a summation of all processor visible to the operating system.
Image 1: Processor Utilization
If we look at the utilization the Process\% User Time is much higher then Processor\% Processor Time, therefore to really see the utilization for SQL when looking at Process counters, we need to average the value to get true utilization. When looking at Processor\% Processor Time, I would like the values to ideally run around maximum of 70-80%, if these values are going above that for extended period of time, then we need to figure out what is causing CPU utilization. Maybe it is time to migrate some workloads off this server, maybe its time to purchase new hardware, or maybe we have runaway queries and need to do some performance tuning. All of this normally depends on having proper baseline of these values. However lets say we do have an issue so now what?
First thing to note, everything in SQL Server uses CPU. Like life, nothing comes free. Memory Reads (Logical Reads), Disk Read/Writes (Physical Reads/Writes), Hash Operations, Execution Plans, etc. all require CPU. Question is what is using up the CPU?
Maybe its physical operations?
If its physical operations I should see my Process\% Privileged Time higher then normal. Generally for SQL server we recommend this value should not exceed 25-30%. In addition to this we should see high disk usage or some kind of memory pressure. That is causing SQL Server to write dirty pages out or high amount of scans causing light number of physical reads. So we potentially need to look at the Logical Disk or Physical Disk counters, SQL Server: Access Methods, and SQL Server: Buffer Manager counters. In SQL Server we might see PAGEIOLATCH_* wait types in sys.dm_os_waiting_tasks DMV.
Maybe its logical operations?
If its logical operations, I should not see high number in Processor\% Privileged Time but should see high number in Process\% User Time. In SQL Server maybe I want to run a query to get the most expensive queries by Logical Reads from sys.dm_exec_query_stats to see who is causing excessive logical reads. You can look at Get Top 50 SQL Statements/Query Plans post on how to get this information.
Maybe its hash operations?
Again this should be user time utilization on CPU and if I have lots of Hash Operations, that means I have potential for high TempDB usage. So I might look for counters SQLServer:Access Methods\Workfiles Created/sec in relation to SQLServer:SQL Statistics\Batch Requests/sec. These values we like to see this ratio to be around 1:5 ratio. In SQL Server we might want to look for queries with high amount of worker time. In addition we might have SOS_SCHEDULER_YEILD wait_type in sys.dm_os_waiting_tasks if the box is being very busy.
Maybe its execution plans?
If its execution plans, then we might have high number of SQLServer:SQL Statistics\SQL Compilations/sec or SQLServer:SQL Statistics\SQL Re-Compilations/sec. If its re-compilations n the sys.dm_exec_query_stats we might find execution plans with high plan_generation_num. Alternatively we might have high compilations/sec because lots of Ad-Hoc T-SQL (any code that is not object with in SQL Server, such as stored procedures, triggers, etc.) code is being executed (greater then your baselines).
SQL Server is running slow but processor is not pinned
Well we looked at some of the scenarios where the CPU might be pinned, how about where CPU is not pinned? What might be issue then? In these cases, if your CPU is not pinned but things are running slow, the problem might be resource contention issue. Therefore you might have blocking in SQL Server. If you look at activity monitor you might see wait types such as LCK_M_*, or ASYNC_NETWORK_IO, etc.
We can look at DMV sys.dm_exec_waiting_tasks, sys.dm_exec_requests, and sys.dm_tran_locks to se where is the contention issue. From there we can further dig into the SQL statement using sys.dm_exec_sql_text and then further the execution plan using sys.dm_exec_query_plan.
In addition you might need to track blocking in SQL Server, please look at Blocked Process Report in SQL Profiler post.
I am sure everyone has run into their fair share of issues, unfortunately the answer is rarely simple, especially when it comes to performance issues. However I hope this post gave you some idea for your further troubleshooting. Another thing to consider is, what if the issue has happened few hours ago or last day and you are just finding about it? It is really outside scope of this post, but something’s that might help in this case is Management Data Warehouse, looking at the previous execution in the DMVs, or using other external monitoring tools such as Microsoft System Center Options Manager.