Sql Server : A connection was successfully established with the server, but then an error occurred during the login process. The certificate chain was issued by an authority that is not trusted

Interesting one today:

Earlier this week, while playing one of our cloud database servers, ran into this confusing error.

Error Message:

A connection was successfully established with the server, but then an error occurred during the 
login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority 
that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

The certificate chain was issued by an authority that is not trusted

Connection Certificate Error

Details:

This is one of those Sql Server errors that are clear and actionable. It clearly indicates that a connection to Sql Server was successfully established, but the encryption certificate (for secure communications) was the problem.

Seems like SSMS received the certificate from the server but it does not seem to be from one of the Trusted Certificate Authorities (CA) — hence the failure in going forward with maintaining the connection.

My guess is that this certificate is used both for identification (authentication) and encryption.

• You are who you say you are (for both parties)
• If I send you something, only you can decrypt it and vice versa

Usually these certificates are issued by CAs, Certificate Authorities, that verify you before giving you a public and private key. But Windows allows us to generate our own certificates for free, but these do not come with seal of approval from CAs.

In our case, its the latter. The certificate sent to SSMS was a generated one and not a CA issues certificate. Hence the error message.

Resolution:

Go to “Connect to Server” pop-up window >> Options >> Check “Trust server certificate“.

Trust Connection Certificate

Once selected, SSMS will accept the remote certificate and complete connection.

Hope this helps,

_Sqltimes

Read Full Post »

Replication Error: Specify all articles when subscribing to a publication using concurrent snapshot processing

Interesting one today:

In our lab environment, while setting up replication on a few nodes, we ran into this interesting error message.

Error:

 

Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 15
Specify all articles when subscribing to a publication using concurrent snapshot processing.

 

At first glance, it did not make much sense — as we did not make any changes to the code. But upon further digging, it became obvious the minor change that was made. @sync_method parameters.

The section where we define the publication, looks like this:

Replication Add Publication

While defining the publication, along with all other parameters, we also mention the way in which the articles & data from Publisher is sent to each Subscriber. @sync_method defines, which approach to take.

The associated ‘add_subscription’ (which threw the error) looks like this:

Replication Add Subscription

Solution:

 

Bingo !!

When a Publication is defined with @sync_method as ‘concurrent‘, then it creates all articles/tables as one chunk of data (not as individual tables) in native mode. So we need to be sure to define @article parameter in ‘sp_addSubscription‘ with ‘all’ as value (and not individual table names) — since the articles are not available individually.

Replication Add Subscription

 

Viola !! Now the add subscription works !!

 

Side note:

 

If you want to add individual tables/articles in the ‘sp_addsubscription‘, then you want to consider using ‘native‘ for @sync_method.

Replication Add Publication

Replication Add Subscription

Hope this helps,

_Sqltimes

Read Full Post »

DBCC CHECKDB Error Msg 5030, Level 16, State 12, Line 1 The database could not be exclusively locked to perform the operation.

Interesting one today:

On a lab machine, when CHECKDB was executed, this seemingly tricky error popped up. This was a lab machine, and there was no traffic or any one connected to it.

Code that we ran:

--
-- CHECKDB error
--
DBCC CHECKDB ('DB')
WITH ALL_ERRORMSGS
	, EXTENDED_LOGICAL_CHECKS
	, TABLOCK
GO

Error Message:

Msg 5030, Level 16, State 12, Line 1 The database could not be exclusively locked to 
perform the operation.

Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot 
could not be created and the database or table could not be locked. 
See Books Online for details of when this behavior is expected and what workarounds 
exist. Also see previous errors for more details.

After checking that there was no traffic or any connections to the database, I re-ran the same CHECKDB a few more times and still ran into the same error.

Reading this MSDB article, some ideas came to mind. The two conditions mentioned in the article are not the root of the problem.

• At least one other connection is using the database against which you run the DBCC CHECK command.
• The database contains at least one file group that is marked as read-only.

Once the TABLOCK is removed, the command worked.

DBCC CHECKDB ('DB')
WITH ALL_ERRORMSGS
	, EXTENDED_LOGICAL_CHECKS
GO

 

Hope this helps,
_Sqltimes

 

 

Read Full Post »

Sql Server : CPU TIME vs. Elapsed Time

Interesting one today:

When we run a query with SET STATISTICS TIME ON, we see CPU Time and Elapsed Time statistics for a query. What is the difference?

SET STATISTICS ON

 

CPU Time is amount of CPU cycles (in time) spent by the workload thread on a particular processor/CPU. This is not an indication of the duration since the query began running on that CPU (there are some nuances — we’ll cover below)

Elapsed Time is the total duration of that thread, the time it took from start to end.

Simple Example:

Let’s say there is a workload thread for a simple query. The query runs for a bit (let’s say 2 seconds) and gets put on hold for a bit (let’s say 5 seconds), and then runs again (let’s say for 1 second) all the way to completion. The time the thread actually spent on CPU is counted towards CPU time (the two times, 2 & 1 seconds — 3 seconds total). Elapsed time is the total duration from when the query was issued to be executed to the time the result was generated — total 8 seconds.

Parallel Processing:

Let’s say there are 4 thread carrying out the workload of a query. Each run on a processor for 1 second and complete all the work in one shot.

Total CPU Time = 4 * 1 = 4 seconds (4 CPU’s with 1 second each)

Elapsed Time = 1 second (from start to finish)

Summary

CPU Time and Elapsed Time measure different time metrics for the same query. One measure the CPU cycles spent (in time) and the other measures the duration. The difference is important in figuring out what problem we are trying to solve.

1. If a query has high Elapsed Time, but low CPU Time, that means, there is some CPU contention or other resource contention (waiting for all resources to be available before the query could be given a CPU).
   1. We want to look for ways to optimize the the way query is written — is it requesting for more resources (rows) than it needs. End user will feel the pain in waiting — if this happens to majority of the queries. 
   2. Adding to this, if the CPUs are all busy all the time, that definitely means we need bigger CPUs for the workload or optimized queries.
   3. Conversely, if the CPU’s are not too busy at this time, that means, the thread is just waiting for other resources (concurrency — locked tables) to be released for this query to progress. Optimize the queries.
2. If CPU Time is higher than Elapsed Time, then there is good amount of parallelism in play.
   1. As long as the CPUs are optimally utilized, this is not a problem, but if you notice more than 80% utilization all the time (the threshold is even lower if they are virtual CPUs), then we need to add extra CPUs to alleviate the situation.
   2. This may not be a problem right now, but it will evolve into a problem soon. One extra long query will snowball everything into high delays — to the point where a user will notice the delays. 

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : The target principal name is incorrect. Cannot generate SSPI context

Interesting Problem Today:

Ran into this issue a few times and every time its a variation of the same headache. So, here some ideas will be documented for posterity.

In general terms, the error looks like this:

Connecting to Subscriber ''
Agent message code 20084. The process could not connect to Subscriber ''
Microsoft SQL Server Native Client 11.0
SQL Server Network Interfaces: The target principal name is incorrect.
Cannot generate SSPI context

The error message seems nebulous and confusing — but for trained eyes it makes perfect sense. For me it took a while to make sense out of it.

There could be several things wrong under the hood, but essentially it says that the target SQL server that is is trying to connect to, does not have a valid SPN with Active Directory.

Meer from Microsoft has documented some information on troubleshooting this issue here. For more details, please read his article, as I’ll over simplify things and address a variation of the problem in this articles (which will be slightly different from his).

Example:

From SQLServerA, using UserA, if I’m trying to connect to SqlServerB, sometimes I get this error. Essentially, means SqlServerB does not have a valid SPN.

Resolution:

First, log into the machine that has SqlServerB. Open command prompt with Administrative privileges. Run the command below to see if there is a valid SPN.

 

SETSPN -L <SQL Server Instance Service Account>

 

If the output looks like the first image below, then the Sql Server instance does not have a valid SPN. Now its time to generate one.

Output without valid SPN:

Invalid SPN

Step 2: Download Kerberos Configuration Manager for SQL Server from here, and start generating one.

Step 3: Open Kerberos Configuration Manager for SQL Server from the same machine that has SqlServerB instance. When you run it, it shows something like this:

 

Kerberos Tool Output

Notice that for Sql Server service, there is not valid SPN or misplaced SPN. So its time to generate one.

Step 4: Hit the “Fix it” button right next to it and generate one. Make sure the user account that is logged into the machine has domain controller permissions.

Step 5: Now run the same command as in Step 1, and the output looks different.

Output with valid SPN:

Valid SPN

 

Voila !! Now you are able to connect to SqlServerB from SqlServerA using UserA

 

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server Replication : Exception Message: The snapshot could not be generated because the publisher is inactive

Interesting one today:

Earlier in our lab environment, ran into this interesting error:

2019-06-20 00:18:46.67 Connecting to Distributor ''
2019-06-20 00:18:46.78 The replication agent had encountered an exception.
2019-06-20 00:18:46.78 Source: Replication
2019-06-20 00:18:46.78 Exception Type: 
Microsoft.SqlServer.Replication.ReplicationAgentException
2019-06-20 00:18:46.78 Exception Message: The snapshot could not be 
generated because the publisher is inactive.
2019-06-20 00:18:46.78 Message Code: 54057
2019-06-20 00:18:46.78

Replication from publisher to subscriber was set up correctly, but when the agent runs, the SQL Agent job stops with this error.

Resolution:

Go to Distributor and run this query to check the status of the publisher instance.

EXEC sp_helpdistpublisher

As we can see, one of the publisher instance is set to Inactive. So now we need to reset it.

Run the script below to change the publisher status at distributor:

EXEC sp_changedistpublisher  @publisher = 'Publisher',  @property = 'active', @value = 'true'
GO

The result looks something like this:

Now  run the previous ‘sp_helpdistpublisher’ again and now the status is active:

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Sql Server : Error : Msg 9514, Level 16, State 1, Line 1 Xml data type is not supported in distributed queries. Remote object has xml column(s)

Interesting error today:

When we query over linked server, we run into some restrictions. One such restrictions is this, where we could not query XML columns over LinkedServer connection.

For example, if the remote server table has a XML column in its structure, when we query the remote server for this table, we run this error:

--
-- Old query that throws this error
--
SELECT ID, XMLColumn
FROM LinkedServer01.DatabaseName.dbo.TableName
GO

Error : Msg 9514, Level 16, State 1, Line 1 
Xml data type is not supported in distributed queries. 
Remote object has xml column(s)

Resolution:

Not sure why this limitation exists, but there is a workaround: OPENQUERY

In the past, we’ve covered some key benefits of OPENQUERY and how it allows us to circumvent some limitations of remote-table-value function calls. Today, we’ll see another benefit of OPENQUERY in circumventing the XML limitation of linked servers.

Rather than querying XML directly, we convert XML into NVARCHAR(MAX) and revert it back to XML once the data is on local server.

--
-- Modified query to circumvent the limitation
--
SELECT ID, CONVERT(XML, XMLColumn) AS XMLcolumn
FROM OPENQUERY(LinkedServer01,
	'SELECT ID
		 , CONVERT(NVARCHAR(MAX), XMLColumn) AS XMLColumn
	FROM DatabaseName.dbo.TableName') AS RemoteQuery
GO

The XML data is retrieved as NVARCHAR(MAX) to the local machine; And immediately converted to XML before displaying.

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : SSMS Refresh Intellisense Cache for Schema Changes

Quick one today:

Intellisense is a great feature. Quite often, we run into situations where we don’t see the drop-down showing new tables, columns, objects, etc names. While this is not frequent, it does happen often enough that it causes some good discussions in conference calls.

Problem Definition:

When we make schema changes in the current session, they are not always visible within intellisense immediately (similarly other people making schema changes to the the database schema are not immediately visible in your session either)

As you can imagine, Intellisense has its own cache and it needs to be refreshed to update with recent schema changes.

Resolution

Two options to fix this:

1. Ctrl + Shift + R
2. Edit menu >> Intellisense >> Refresh Local Cache

Hope this helps,
_Sqltimes

 

Read Full Post »

Sql Server Error: Msg 2207, Level 16, State 1, Line 89 XQuery : Only non-document nodes can be inserted. Found “xs:string ?”.

Interesting Error today:

While playing with XML DML, ran into this cryptic error.

Msg 2207, Level 16, State 1, Line 89
XQuery [dbo.Table.XMLColumn.modify()]: Only non-document nodes 
can be inserted. Found "xs:string ?".

 

While INSERTing a new XML element into an existing XML column, using the below code, this error pops up:

 

DECLARE @NewXMLElement VARCHAR(50) = 'Test'

UPDATE dbo.TableName
SET XMLColumn.modify('
		insert sql:variable("@NewXMLElement")
		into (/Parent/Child)[1]
	')
WHERE XMLColumn.exist('(/Parent/Child/NewXMLElement/text())') = 0
GO

Resolution:

Here, the goal is to INSERT a XML snippet into an existing XML column value. So, it is important that in the modify() function, the new XML element variable is defined as XML datatype

DECLARE @NewXMLElement XML = 'Test'

UPDATE dbo.TableName
SET XMLColumn.modify('
		insert sql:variable("@NewXMLElement")
		into (/Parent/Child)[1]
	')
WHERE XMLColumn.exist('(/Parent/Child/NewXMLElement/text())') = 0
GO

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Sql Server : Msg 3202, Level 16, State 1, Line 1 Write on failed: 112(There is not enough space on the disk.) Msg 3013, Level 16, State 1, Line 1

Interesting one today:

On a production box, the backup jobs have been failing with an interesting and perplexing error. Its says “Not enough disk space“; As you can guess, this is one of those confusing or misleading error messages that’s not what it seems on the surface — Making it worthwhile for a post of its own.

Detailed error message is below:

BACKUP DATABASE DummyDB
TO        DISK = N''
	, DISK = N''
	, DISK = N''
	, DISK = ''
WITH STATS = 1
GO

...
...
...
68 percent processed. 
69 percent processed. 
70 percent processed. 
Msg 3202, Level 16, State 1, Line 1 

Write on "F:\MSSQL\Backup\DummyDB.BAK" failed: 
112(There is not enough space on the disk.) 

Msg 3013, Level 16, State 1, Line 1 
BACKUP DATABASE is terminating abnormally.

This error occurs in both backups with & without compression; And in FULL & Differential backups.

This is a fairly large database, ranging up to 18 TB. So, backups are an ordeal to perform. So, when DIFF backups started failing, it was a bit concerning too.

After attempting several backups on local  & remote storage with plenty of space, a pattern still did not emerge. The only constant is that it fails around 70% completion progress.

At that point, one of  my colleagues (Thanks Michael) pointed out that, as part of backup operation, Sql Server will first run some algorithm that calculates the amount of space needed for the backup file. If the backup drive has enough free space well  and good, if not, it throws this error.

But, as you can guess, we had plenty of free space i.e. peta bytes of free space.

Occasionally, manual backups are successful. So, I’m not sure what is going on, but here is my theory:

At different points, Sql  Server  runs the algorithm (“pre-allocation algorithm”) to determine if there is enough space. Initially it comes back saying “yes” — and the backup proceeds with writing to the backup file; Again a little later, it checks, and it comes back with “Yes”; But at someone on subsequent checks (in our case between 70% to 72% complete), the algorithm decides there is  not enough disk space.

So, turns out there is a TRACE FLAG called 3042 that could disable this algorithm from making any assessments — that way backups could progress to completion.

From  MSDN:

Bypasses the default backup compression pre-allocation algorithm to allow the backup file to grow only as needed to reach its final size. This trace flag is useful if you need to save on space by allocating only the actual size required for the compressed backup. Using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation).

Using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation).

Caution: Manually make sure there is plenty of space for backup to complete — since we are disabling the algorithm.

--
-- Disable pre-allocation algorithm
--
DBCC TRACEON (3042)
GO

BACKUP DATABASE DummyDB
TO        DISK = N''
    , DISK = N''
    , DISK = N''
    , DISK = ''
WITH STATS = 1
GO
DBCC TRACEOFF (3042)
GO

Make sure you test this in a non-production environment, before enabling it in production.

Hope this helps,
_Sqltimes

Read Full Post »