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 »

Sql Server Trace Flags 1211 and 1224

Interesting one today:

A while ago, we looked at the lock escalation patterns within Sql Server. Today, we’ll cover a couple of trace flags that impact that lock escalation. Thanks to my friend Bob for this idea.

• Trace Flag 1211
• Trace Flag 1224

Trace Flag 1211: 

In short, this flag prevents Sql Server from escalating locks under any circumstances.

Within Sql Server, as we saw in the previous article, Sql Server prefers to carry out the work with least number of locks at the least level of locks. If work could be carried out with Row Locks, then Sql Server would issue necessary row locks on the records and carry out the task. When the number of row locks goes beyond a threshold, then it escalates to page-lock or table lock.

This default behavior could be altered with the trace flag 1211. When we enable this trace flag, Sql Server will not escalate locks. If it has row locks, it will carry out the work with row-locks, even if more and more row locks are needed (and even if its efficient to carry out the operation with fewer page locks or a single table lock).

More locks causes more memory pressure — as each lock takes certain amount of memory space. So even under memory pressure, it will work within current lock levels. If and when it runs out of memory, it issue an error (1204). 

This is a powerful trace flag, so its better to not use it. There is a better alternative. Trace Flag 1224.

Trace Flag 1224: 

In short, Sql Server prefers to complete the operation under current lock-level, but if that incurs memory pressure, then it escalates locks. This is a much better way to handle lock escalation than trace flag 1211.

Note: If both the flags are enabled, then trace flag 1211 takes precedence over 1224. So there will not be any lock escalation even under memory pressure.

Note: Use this script to enable or disable a trace flag or to check trace flags status. 

Hat tip to Bob for the idea.

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : Enable a Sql Agent Job using T-SQL query

Quick one today:

Recently, we saw a time-saving technique in starting job using T-sql query.

Today, we’ll look at another one. This comes in handy when dealing with multiple jobs — where handling them is more efficient using T-SQL than UI. Even during deployments, where we need to disable jobs at the beginning of the deployment and then enable them at the end.

--
--	Enable a Sql Agent Job
--
USE msdb
GO
EXEC dbo.sp_update_job    @job_name = N'SomeJobName'
						, @enabled = 1
GO  

 

This is simple and elegant way to enable/disable one or many jobs at the same time, making interaction with SQL Server more efficient. Just query job names from

--
--    Get list of job names
--
SELECT * FROM msdb..sysjobs
GO

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : Change Login Password using TSQL Query

Quick one today:

Occasionally, we need to change the password of a login within SQL Server. Following T-SQL template is handy query to carry out that operation.

--
-- Change login password using T-SQL
--
ALTER LOGIN [Login] 
WITH PASSWORD = 'new password'
GO

 

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 »