<?xml version="1.0" encoding="UTF-8"?><rss version="2.0"
	xmlns:content="http://purl.org/rss/1.0/modules/content/"
	xmlns:wfw="http://wellformedweb.org/CommentAPI/"
	xmlns:dc="http://purl.org/dc/elements/1.1/"
	xmlns:atom="http://www.w3.org/2005/Atom"
	xmlns:sy="http://purl.org/rss/1.0/modules/syndication/"
	xmlns:slash="http://purl.org/rss/1.0/modules/slash/"
	>

<channel>
	<title>SQL Server Blog Forum</title>
	<atom:link href="http://sqlserverblogforum.com/feed/" rel="self" type="application/rss+xml" />
	<link>https://sqlserverblogforum.com</link>
	<description>Guide to SQL Server DBAs and Developers</description>
	<lastBuildDate>Mon, 09 Jan 2023 09:36:14 +0000</lastBuildDate>
	<language>en-US</language>
	<sy:updatePeriod>
	hourly	</sy:updatePeriod>
	<sy:updateFrequency>
	1	</sy:updateFrequency>
	<generator>https://wordpress.org/?v=6.1.1</generator>

<image>
	<url>https://sqlserverblogforum.com/wp-content/uploads/2022/10/cropped-sql-32x32.jpg</url>
	<title>SQL Server Blog Forum</title>
	<link>https://sqlserverblogforum.com</link>
	<width>32</width>
	<height>32</height>
</image> 
	<item>
		<title>Login failed for user Reason: Token-based server access validation failed with an infrastructure error Login lacks Connect SQL permission.</title>
		<link>https://sqlserverblogforum.com/login-failed-for-user-reason-token-based-server-access-validation-failed-with-an-infrastructure-error-login-lacks-connect-sql-permission/</link>
					<comments>https://sqlserverblogforum.com/login-failed-for-user-reason-token-based-server-access-validation-failed-with-an-infrastructure-error-login-lacks-connect-sql-permission/#respond</comments>
		
		<dc:creator><![CDATA[Muthukkumaran]]></dc:creator>
		<pubDate>Wed, 04 Jan 2023 06:41:23 +0000</pubDate>
				<category><![CDATA[DBA]]></category>
		<category><![CDATA[Login failed for user Reason: Token-based server access validation failed]]></category>
		<category><![CDATA[Login lacks Connect SQL permission]]></category>
		<guid isPermaLink="false">https://sqlserverblogforum.com/?p=2765</guid>

					<description><![CDATA[Login failed for user Reason: Token-based server access validation failed with an infrastructure error Login lacks Connect SQL permission.   Login failed for user &#8216;localhost\windows_user&#8217;. Reason: Token-based server access validation failed with an infrastructure error. Login lacks Connect SQL permission. [CLIENT: &#60;local machine&#62;] &#160; Check you have permission to the user, generally we will have permission. Check you are able to access SSMS local &#38; remote. Most of the case issue will be &#8211; Check how many group has deny permission, definitely your login will be one of in the group.  For our case it was built in user group. SELECT sp.[name],sp.type_desc FROM sys.server_principals sp INNER JOIN sys.server_permissions PERM ON sp.principal_id = PERM.grantee_principal_id WHERE PERM.state_desc = 'DENY' &#160;]]></description>
		
					<wfw:commentRss>https://sqlserverblogforum.com/login-failed-for-user-reason-token-based-server-access-validation-failed-with-an-infrastructure-error-login-lacks-connect-sql-permission/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>MSSQL Query running fast in production and slow in non-production</title>
		<link>https://sqlserverblogforum.com/mssql-query-running-fast-in-production-and-slow-in-non-production/</link>
					<comments>https://sqlserverblogforum.com/mssql-query-running-fast-in-production-and-slow-in-non-production/#respond</comments>
		
		<dc:creator><![CDATA[Muthukkumaran]]></dc:creator>
		<pubDate>Wed, 21 Sep 2022 09:59:22 +0000</pubDate>
				<category><![CDATA[Performance]]></category>
		<category><![CDATA[SQL Statistics]]></category>
		<guid isPermaLink="false">https://sqlserverblogforum.com/?p=2725</guid>

					<description><![CDATA[ Query running fast in production and slow in non-production I have asked to look one of the queries that run fast in production and slow in non-production. Question asked &#8211; how long it took to complete the results. It is about 1.50 minutes in non-production, but in production it completed less than 5 seconds. Got the code and run myself in SSMS to compare the plan, since it will finish 1.50 minutes. It quickly showed estimated number of rows are totally different. This could be either out of stats or data skew. &#160; I just checked the last stats update and updated the stats that fixed the issue. You can easily compare execution plan in SSMS. &#160;]]></description>
		
					<wfw:commentRss>https://sqlserverblogforum.com/mssql-query-running-fast-in-production-and-slow-in-non-production/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>MS SQL server remote DBA training course topics Chennai India</title>
		<link>https://sqlserverblogforum.com/ms-sql-server-remote-training-course-topics-chennai-india/</link>
					<comments>https://sqlserverblogforum.com/ms-sql-server-remote-training-course-topics-chennai-india/#respond</comments>
		
		<dc:creator><![CDATA[Muthukkumaran]]></dc:creator>
		<pubDate>Fri, 22 Jul 2022 03:31:42 +0000</pubDate>
				<category><![CDATA[DBA]]></category>
		<category><![CDATA[MSSQL DBA course]]></category>
		<category><![CDATA[MSSQL DBA Tamil training course topics]]></category>
		<guid isPermaLink="false">https://sqlserverblogforum.com/?p=2723</guid>

					<description><![CDATA[Microsoft SQL server training course topics: &#160; Overview of IT environment with daily DBA activity and health check A brief of history (RDBMS &#38; DBMS) SQL server internal architecture SQL server overview (Editions) SQL Installation overview (System DBs) Review of new features Transact -SQL overview Normalization TABLES AND CONSTRAINTS Standard data types Tables and columns in SQL server Objects, Tables and columns in T-SQL Modifying tables and columns Database diagram architecture Constrains TRANSACT SQL QUIERES Querying SQL server tables SQL and transact SQL Using the select statement Transact SQL built-in function Summary queries Sub queries MERGING DATA WITH JOINS&#38;UNIONS Working with inner joins Using left outer joins Multiplying data with cross joins Referencing self –joins Working with complex joins Nested loop join Merge join Hash join Join hints in SQL queries AGGREGRATING DATA Using aggregate function Grouping a data set Nesting aggregations Filtering groups &#38;adding grand totals MERGE statement VIEWS Uses of views Limitation of views Calculated columns Creating views Overview of indexed views INDEXES Overview of indexes Clustered versus non-clustered indexed Filtered index Creating indexed Using SHOW PLAN to see if the index is being used Using composite indexes to cover queries Duplicate keys and rows and how to solve problem The distribution page and update statistics Understanding the index statistics steps TRANSACTIONS How data is changed in SQL server Manipulating data within transactions Transactions processing commands Transaction isolation levels Locking issues Using sp- lock, Whoisactive Table hints(locks) PROGRAMING BASISCS Variables Flow control statements Error handling Using case expressions Working with nulls&#38; coalesce Working with dates and times Parsing strings Converting data USER DEFINEDD FUNCTIONS Scalar function Table function Appropriate usage of function STORED PROCEDURES Creating stored procedures Passing parameters Returning values and updating parameters Compilation problems and methods Temporary procedures and auto start procedures TRIGGERS Basic trigger assumptions and syntax Improving trigger performance Checking the number of rows affected Nested triggers Ensuring integrity with triggers   MONITORING SQL SERVER Viewing current activity Using DDL triggers Using event notifications AUTOMATING ADMINISTRATIVE TASKS Automating administrative tasks in SQL server Configuring the SQL server agent Creating jobs and operations Creating alerts Managing multiple servers Managing SQL server agent security Dynamic Management Views (DMVs &#38; DMFs) Understanding DMVs and DMFs Use of DMVs and DMFs INSTALLING AND CONFIGURATION SQL SERVER 2005 Preparing to install SQL server Installing SQL server Managing a SQL server installation DATA STORAGE &#38; MANAGEMENT How is data organized in SQL server? Creating a database Using file groups Shrinking database MANAGING Server roles Data base roles User mappings LOGINS AND USERS Authentication and authorization IMPELEMENTING DISASTER RECOVERY STRATEG Implementing a backup strategy Implementing a recovery model strategy Plan a backup strategy Backup user database Restore user database Backup and restore system databases Restoring database Rebuild the master database MAINTAINING HIGH AVAIABILITY Introduction to high availability Implementing Always ON availability groups Implementing server clustering Implementing database mirroring Implementing log shipping INTRODUCTION TO REPLICATION Overview of replication Replication scenarios IMPLEMENTING REPLICATION Creating a publication Creating a subscription Describe replication and its component Describe common  replication scenarios LOG SHIPPING AND DATABASE MIRRORING Configuring transaction log shipping Understanding database mirroring requirement Configuring database mirroring PERFORMANCE AND SQL SERVER Query tuning methods Execution plan reading Choosing and Building indexes online SQL statistics update Transact SQL improvements Monitoring resource consumption Establishing baseline metrics SQL server management studio SQL server configuration manager Database engine tuning advisor SQL server profiler Availability and scalability DATABASE DESIGN FOR PERFORMANCE Introducing logical Database design for performance Introducing physical Database design Data volume analysis Transaction analysis Hardware environment consideration SQL SERVER AND WINDOWS How SQL server uses CPU Priority Use of symmetric multiprocessing system Thread use Query parallelism Investigation CPU bottlenecks Solving problems with CPU SQL server and memory An overview of windows virtual memory management How SQL server uses memory Configuring memory for SQL server Investigation memory bottlenecks SQL server and disk I/O An overview of the data cache Keeping tables and indexes in cache Investigation disk I/O bottlenecks ARCHITECTURAL PERFORMANCE OPTIONS AND CHOICES Storage Raid array MONITORING PERFORMANCE System stored procedures System monitor, performance logs , and alerts Client statics The SQL server profiler Database engine tuning advisor SQL SERVER SSIS &#38; SSRS walk through.]]></description>
		
					<wfw:commentRss>https://sqlserverblogforum.com/ms-sql-server-remote-training-course-topics-chennai-india/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>DR Test Backup and restore drill test</title>
		<link>https://sqlserverblogforum.com/dr-test-backup-and-restore-drill-test/</link>
					<comments>https://sqlserverblogforum.com/dr-test-backup-and-restore-drill-test/#respond</comments>
		
		<dc:creator><![CDATA[Muthukkumaran]]></dc:creator>
		<pubDate>Wed, 08 Jun 2022 05:28:33 +0000</pubDate>
				<category><![CDATA[DBA]]></category>
		<guid isPermaLink="false">https://sqlserverblogforum.com/?p=2697</guid>

					<description><![CDATA[Overview: There was requirement from business to test live server backup into DR server every month / week, not a completed DR. It was about 30+ databases with total of 500 GB. Steps: Add net use command in sp_DatabaseRestore stored procedure Share backup folder path in source server &#8220;\&#8221; Run the find the recent backup script on source server Script out and run it on target server where you have created sp_DatabaseRestore Run the restore command in SSMS or SQL agent job. Test Restore with execute N parameter and make sure everything is correct and run with Y. &#160; I have started using Ola Hallengren’s maintenance script and searched for restore script found Brent Ozar’s website. &#160; Here are the steps to test: Use Ola Hallengren’s maintenance script Create Brent Ozar’s First Responder Kit. Modify the SP sp_DatabaseRestore and add &#8220;exec xp_cmdshell &#8216;net use \x.x.x.x\Database_Backup /USER:domain\user password'&#8221; to allow permission in the beginning of the SP to access the backup share from DR server. Enable xp_cmdshell sp_configure 'show advan',1;reconfigure sp_configure 'xp_cmdshell',1;reconfigure Share backup folder path in source server &#8220;\&#8221; (OR) If you cannot share it, copy and paste the backup target and adjust the script based on the path. Run this find the recent backup on source server WITH LastBackUp AS ( SELECT bs.database_name, bs.backup_size, bs.backup_start_date, --replace (bmf.physical_device_name,'G:','\\x.x.x.x') as physical_device_name, replace(LEFT(bmf.physical_device_name,CHARINDEX('\FULL\',bmf.physical_device_name,1) + LEN('FULL\')),'G:','\\x.x.x.x') as physical_device_name, Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC ) FROM msdb.dbo.backupmediafamily bmf JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id WHERE bs.[type] = 'D' AND bs.is_copy_only = 0 ) --EXEC dbo.sp_DatabaseRestore @Database = 'dbPainTrax_2',@BackupPathFull = '\\x.x.x.x\Dropbox\Database_Backup\',@ContinueLogs = 0,@RunRecovery = 0; SELECT 'EXEC dbo.sp_DatabaseRestore @Database ='''+sd.name+''',@BackupPathFull ='''+physical_device_name+''',@ContinueLogs = 0,@RunRecovery = 1 ,@MoveFiles=1,@MoveDataDrive=''E:\SQL_DataBase\'',@MoveLogDrive=''F:\SQLDatabase\'',@Execute = ''Y'';' as cmd, sd.name AS [Database], CAST(backup_size / 1048576 AS DECIMAL(10, 2) ) AS [BackupSizeMB], backup_start_date AS [Last Full DB Backup Date], physical_device_name AS [Backup File Location] FROM sys.databases AS sd LEFT JOIN LastBackUp AS lb ON sd.name = lb.database_name AND Position = 1 where database_name not in ('master','model','msdb','tempdb') and database_name not like 'dba%' ORDER BY [backup_start_date]; /* EXEC dbo.sp_DatabaseRestore @Database ='db_PharmaTrax_Test_New', @BackupPathFull ='\\x.x.x.x\Dropbox\TechSupport\Database_Backup_Daily\WIN-VMC-SQL001$SQLEXPRESS\db_PharmaTrax_Test_New\FULL\' ,@RunRecovery = 1,@ContinueLogs = 0,@Execute = 'N',@MoveFiles=1,@MoveDataDrive='E:\SQLDataBase\',@MoveLogDrive='F:\SQLDatabase\'; */ Script out and run it on target server where you have created sp_DatabaseRestore Run the restore command in SSMS or SQL agent job. Test Restore with execute N parameter and make sure everything is correct and run with Y. --/* -- Enable xp_cmdshell -- Create SP sp_DatabaseRestore https://www.brentozar.com/archive/2017/03/databaserestore-open-source-database-restore-stored-procedure/ -- Alter SP with net use to allow permission to the prod backup share -- Run recent-backup-finding.sql to get the data, copy and paste the CMD column into new query windwos and run ex: EXEC dbo.sp_DatabaseRestore @Database ='DBA_Trace_Load_14_feb_2022',@BackupPathFull ='\\x.x.x.x\Database_Backup_Daily\xx-SQL001$SQLEXPRESS\DBA_Trace_Load_14_feb_2022\FULL\',@ContinueLogs = 0,@RunRecovery = 1; --*/ sp_configure 'show advan',1;reconfigure sp_configure 'xp_cmdshell',1;reconfigure exec xp_cmdshell 'net use \\x.x.x.x\Dropbox\Database_Backup /USER:x.x.x.x\Administrator password' exec xp_cmdshell 'net use \\x.x.x.x\Dropbox\Database_Backup /DELETE /y' --Run with -- @Execute = 'N' and find out your restore is correct and Data &#38; log path are correct EXEC dbo.sp_DatabaseRestore @Database ='dbname', @BackupPathFull ='\\x.x.x.x\Dropbox\TechSupport\Database_Backup_Daily\WIN-VMC-SQL001$SQLEXPRESS\dbname\FULL\' ,@RunRecovery = 1,@ContinueLogs = 0,@Execute = 'N',@MoveFiles=1,@MoveDataDrive='E:\SQL_DataBase\',@MoveLogDrive='F:\SQL_Database\'; &#160; Complete script available here in my GitHub Hope this would help someone.]]></description>
		
					<wfw:commentRss>https://sqlserverblogforum.com/dr-test-backup-and-restore-drill-test/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>Msg 22906, Level 16, State 1, Procedure sp_cdc_enable_db_internal cannot be enabled for Change Data Capture because a database user named cdc</title>
		<link>https://sqlserverblogforum.com/msg-22906-level-16-state-1-procedure-sp_cdc_enable_db_internal-cannot-be-enabled-for-change-data-capture-because-a-database-user-named-cdc/</link>
					<comments>https://sqlserverblogforum.com/msg-22906-level-16-state-1-procedure-sp_cdc_enable_db_internal-cannot-be-enabled-for-change-data-capture-because-a-database-user-named-cdc/#respond</comments>
		
		<dc:creator><![CDATA[Muthukkumaran]]></dc:creator>
		<pubDate>Wed, 04 May 2022 08:05:21 +0000</pubDate>
				<category><![CDATA[DBA]]></category>
		<category><![CDATA[Msg 22906 Level 16 State 1]]></category>
		<guid isPermaLink="false">https://sqlserverblogforum.com/?p=2675</guid>

					<description><![CDATA[Overview: There was a database restore from production to UAT and the restore was done without Keep CDC option. When developer asked to enable CDC the following was the error. Always keep CDC while restore is best option whether we have CDC or not. use [DB Name] go exec sys.sp_cdc_enable_db Msg 22906, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 49 [batch start line 2] The database &#8216;DBName&#8217; cannot be enabled for Change Data Capture because a database user named &#8216;cdc&#8217; or a schema named &#8216;cdc&#8217; already exists in the current database. These objects are required exclusively by Change Data Capture. Drop or rename the user or schema and retry the operation. Solution: Drop all CDC objects and enable CDC again. use [T] go exec sys.sp_cdc_enable_db -- Remove tables SELECT 'drop table cdc.' + QUOTENAME(t.name) AS name FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE s.name = 'cdc' -- Remove SP SELECT 'drop procedure cdc.' + QUOTENAME(pr.name) AS name FROM sys.procedures pr JOIN sys.schemas s ON pr.schema_id = s.schema_id WHERE s.name = 'cdc' -- Remove functions SELECT 'drop function cdc.' + QUOTENAME(fn.name) AS name FROM sys.objects fn JOIN sys.schemas s ON fn.schema_id = s.schema_id WHERE fn.type IN ( 'FN', 'IF', 'TF' ) AND s.name = 'cdc' -- Change AUTHORIZATION schema role SELECT 'ALTER AUTHORIZATION ON ROLE::'+DP1.name+' to DBO' , DP1.name FROM sys.database_principals AS DP1 JOIN sys.database_principals AS DP2 ON DP1.owning_principal_id = DP2.principal_id WHERE DP1.type = 'R' AND DP2.name = 'cdc'; -- Remove schema and user go DROP SCHEMA [cdc] GO DROP USER [cdc] GO &#160;]]></description>
		
					<wfw:commentRss>https://sqlserverblogforum.com/msg-22906-level-16-state-1-procedure-sp_cdc_enable_db_internal-cannot-be-enabled-for-change-data-capture-because-a-database-user-named-cdc/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>identity jumping from default to bigger large number</title>
		<link>https://sqlserverblogforum.com/identity-jumping-from-default-to-bigger-large-number/</link>
					<comments>https://sqlserverblogforum.com/identity-jumping-from-default-to-bigger-large-number/#respond</comments>
		
		<dc:creator><![CDATA[Muthukkumaran]]></dc:creator>
		<pubDate>Wed, 09 Mar 2022 07:52:59 +0000</pubDate>
				<category><![CDATA[DBA]]></category>
		<category><![CDATA[Trace flag T272]]></category>
		<guid isPermaLink="false">https://sqlserverblogforum.com/?p=2635</guid>

					<description><![CDATA[Overview of the issue: It is small web based productions application and hosted in SQL express 2012 for the license costing. Issue is whenever SQL restarted for whatever reason identity numbers are changed very bigger and identity jumping from one to other digits. To fix this in our case we cannot do this at database level or table schema script level to NOCACHE or disable the IDENTITY CACHE, since it is been enabled many databases and application code changes are not advised. Resolution: Enable trace flag T272 at instance level in the start up parameters. &#160;]]></description>
		
					<wfw:commentRss>https://sqlserverblogforum.com/identity-jumping-from-default-to-bigger-large-number/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>Msg 22830, Level 16, State 1, Procedure sys.sp_cdc_enable_db_internal</title>
		<link>https://sqlserverblogforum.com/msg-22830-level-16-state-1-procedure-sys-sp_cdc_enable_db_internal/</link>
					<comments>https://sqlserverblogforum.com/msg-22830-level-16-state-1-procedure-sys-sp_cdc_enable_db_internal/#respond</comments>
		
		<dc:creator><![CDATA[Muthukkumaran]]></dc:creator>
		<pubDate>Wed, 16 Feb 2022 16:23:17 +0000</pubDate>
				<category><![CDATA[DBA]]></category>
		<category><![CDATA[Msg 22830 Level 16 State 1]]></category>
		<guid isPermaLink="false">https://sqlserverblogforum.com/?p=2669</guid>

					<description><![CDATA[Msg 22830, Level 16, State 1, Procedure sys.sp_cdc_enable_db_internal, Line 198 [Batch Start Line 8]Could not update the metadata that indicates database is enabled for Change Data Capture. The failure occurred when executing the command &#8216;SetCDCTracked(Value = 1)&#8217;. The error returned was 15404: &#8216;Cannot not obtain information about windows NT group error code 0x5. &#160; Change your database owner to SA. use [DB_name] go Exec sp_changedbowner'sa' &#160; &#160; &#160; &#160;]]></description>
		
					<wfw:commentRss>https://sqlserverblogforum.com/msg-22830-level-16-state-1-procedure-sys-sp_cdc_enable_db_internal/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>Cannot connect to WMI provider. You do not have permission or the server is unreachable SQL server 2005</title>
		<link>https://sqlserverblogforum.com/cannot-connect-to-wmi-provider-you-do-not-have-permission-or-the-server-is-unreachable-sql-server-2005/</link>
					<comments>https://sqlserverblogforum.com/cannot-connect-to-wmi-provider-you-do-not-have-permission-or-the-server-is-unreachable-sql-server-2005/#respond</comments>
		
		<dc:creator><![CDATA[Muthukkumaran]]></dc:creator>
		<pubDate>Wed, 05 Jan 2022 15:35:01 +0000</pubDate>
				<category><![CDATA[DBA]]></category>
		<category><![CDATA[SQL server configuration manager is not opening]]></category>
		<guid isPermaLink="false">https://sqlserverblogforum.com/?p=2630</guid>

					<description><![CDATA[Overview of the issue. We had two SQL instances are installed in a server SQL 2008R2 and 2014 both instance configuration managers are not opened. Error: Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Fix: Open drive where you have installed SQL, mostly C and navigate the folders and make sure you have file called &#8211; sqlmgmproviderxpsp2up.mof. Run  the following command in the CMD with run as administrator. mofcomp “%programfiles(x86)%\Microsoft SQL Server\120\Shared\sqlmgmproviderxpsp2up.mof” &#160; Ex: 120 need to be changed for other versions. &#160; 2005-90 2008R2-100 2012-110 2014-120 2016-130 2017-140 2019-150]]></description>
		
					<wfw:commentRss>https://sqlserverblogforum.com/cannot-connect-to-wmi-provider-you-do-not-have-permission-or-the-server-is-unreachable-sql-server-2005/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>SPN registration to SQL server to make a Kerberos authentication</title>
		<link>https://sqlserverblogforum.com/spn-registration-to-sql-server-to-make-a-kerberos-authentication/</link>
					<comments>https://sqlserverblogforum.com/spn-registration-to-sql-server-to-make-a-kerberos-authentication/#respond</comments>
		
		<dc:creator><![CDATA[Muthukkumaran]]></dc:creator>
		<pubDate>Wed, 01 Dec 2021 08:41:46 +0000</pubDate>
				<category><![CDATA[DBA]]></category>
		<category><![CDATA[SPN SQL]]></category>
		<category><![CDATA[sql server spn issue double hop authentication]]></category>
		<category><![CDATA[T sql to check Kerberos or NTLM authentication]]></category>
		<guid isPermaLink="false">https://sqlserverblogforum.com/?p=2620</guid>

					<description><![CDATA[Overview of the issue. It is UAT server which has two SQL instances older 2008R2 and newer 2014. Application team is managing it their own. The issue is application is not connecting after migrated databases from 2008R2 to 2014, even connection string been changed. I did basic connectivity checks, I can able to connect from other windows server where SSMS been installed. But unfortunately, I cannot test it from application server since it was UNIX server and application is JAVA based one. I have checked SQL logs and had logon trace as well to find any incoming connection and failures etc. I could not get any, then connection string been shared and there is an explicit call to Kerberos authentication. It gave me a clue and checked the SQL 2014, SPN was not registered. Resolution: How to register SPN to SQL server to make a Kerberos authentication. We need SQL service to run on domain account not in the local account. Make sure to have static port and by default SQL named instance will use dynamic port. Register SPN in the domain controller manually with help of domain admin &#160; For example: Instance name is Muthu and port is 1550 SETSPN –S MSSQLSvc/YOURSERVERNAME.mydomain.com:1550 mydomain\svcAcct SETSPN –S MSSQLSvc/YOURSERVERNAME.mydomain.com:Muthu mydomain\svcAcct For my case: It was not run under domain account, it is NT account. Changed to domain account with static port after a restart of SQL service, SPN got auto registered. Check SQL log, SPN got registered or not. SP_READERRORLOG 0,1,'SPN' How to check SQL server is using Kerberos or NTLM authentication SELECT S.SESSION_ID, C.CONNECT_TIME, S.LOGIN_TIME, S.LOGIN_NAME, C.PROTOCOL_TYPE, C.AUTH_SCHEME, S.HOST_NAME, S.PROGRAM_NAME FROM SYS.DM_EXEC_SESSIONS S JOIN SYS.DM_EXEC_CONNECTIONS C ON S.SESSION_ID = C.SESSION_ID WHERE C.AUTH_SCHEME LIKE 'k%' &#160;]]></description>
		
					<wfw:commentRss>https://sqlserverblogforum.com/spn-registration-to-sql-server-to-make-a-kerberos-authentication/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>SQL query tuning front end Website slowness</title>
		<link>https://sqlserverblogforum.com/sql-query-tuning-front-end-website-slowness/</link>
					<comments>https://sqlserverblogforum.com/sql-query-tuning-front-end-website-slowness/#respond</comments>
		
		<dc:creator><![CDATA[Muthukkumaran]]></dc:creator>
		<pubDate>Wed, 10 Nov 2021 11:17:03 +0000</pubDate>
				<category><![CDATA[Performance]]></category>
		<category><![CDATA[Query slowness]]></category>
		<category><![CDATA[website slow sql]]></category>
		<guid isPermaLink="false">https://sqlserverblogforum.com/?p=2594</guid>

					<description><![CDATA[ SQL query tuning front end Website slowness Overview of the issue. There is a health care website which used by doctors, in a day which runs slow sometimes and runs fine most of the time. The slowness is about 1 to 3 seconds and it sometimes timeout as well, when it reaches timeout setting. I have configured server side trace, whoisactive and Perfmon counters as well to correlate with anything when needed. As a basic check, the website is fetching data from two databases one is 300 MB and other is 500 MB and no maintenance job been run in place. Have configured maintenance job, still same issue after a re index etc  run. In the initial, I thought it is tiny database is not going to cause anything, it could easily fit into memory, my prediction is wrong. I have got the stored procedure and runtime and other metrics from trace and got captured from GUI profiler as well. There was a stored procedure which has high cost in three operators, they are key lookups and scans, have made them to seek, after that it has an optimal runtime. It has parameters as well, let us look any PSP issue as well in the future. Comparison of both run time. Cannot attach the query plans. Here is CPU Perfmon data, top one is when we had issue and there was no maintenance job in place and bottom one is after tuning and every Saturday there was a spike cause of checkDB. Do not guess by looking the database size 🙂]]></description>
		
					<wfw:commentRss>https://sqlserverblogforum.com/sql-query-tuning-front-end-website-slowness/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
	</channel>
</rss>
