Sql Server Error : An invalid parameter or option was specified for procedure ‘sys.sp_change_users_login’

Interesting one today:

Occasionally, when we restore databases from backups, we need to fix orphan users. Recently, a few years ago, we discussed about fixing orphan users using script. Here we’ll look at a scenario, where the regular orphan users script will not work. 

Error Message:

Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 214 [Batch Start Line 61]
An invalid parameter or option was specified for procedure ‘sys.sp_change_users_login’.

The script “EXEC sp_change_users_login 'Auto_Fix', 'user'” only works for Sql Authentication based logins and not for Windows-level principals or non-login based users. See MSDN article for more details.

A user without login would look like this, in SSMS:

Sql Users Without Login

In such scenarios,

• sp_change_users_login cannot be used to map database users to Windows-level principals, certificates, or asymmetric keys.
• sp_change_users_login cannot be used with a SQL Server login created from a Windows principal or with a user created by using CREATE USER WITHOUT LOGIN.

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : View or function has more column names specified than columns defined

Interesting one today:

In our development environment, sometimes we run into this error that’s a bit nebulous.

Error Message:
SQLException for SQL []; SQL state [S0001]; error code [4502]; View or function has more
column names specified than columns defined.;
nested exception is com.microsoft.sqlserver.jdbc.SQLServerException:
View or function has more column names specified than columns defined.

Occasionally, we make changes to the tables schema and when you have Views that depend on these tables, they become stale. While the definition is still intact, the meta-data becomes stale, so they need to be refreshed.

This refresh uncovers if there are any conflicts between view definitions and the underlying tables. 

--
-- Refresh View metadata 
--
EXEC sp_refreshview 'dbo.View_SomeTable'
GO 

Once the underlying conflicts are resolved, if any, the view will execute correctly from application. Just refreshing the meta-data helps sometimes.

 

Hope this helps,

_Sqltimes

Read Full Post »

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 »

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: Query to check Database Mail was successful

Quick one today:

While not used every time or ever where, Database Mail is a valuable tool in Sql Server. It elevates any in-house monitoring solutions — and provides a key piece to send alert emails. Today, we’ll look at how to check if the emails queued are sent or failed.

--
-- Check the status of emails in MSDB
--
SELECT mailitem_id, subject, recipients, send_request_date, send_request_user, sent_status, sent_date
FROM msdb.dbo.sysmail_allitems
ORDER BY mailitem_id DESC
GO

DB Mail Status

There are more system tables that helps capture the status of unsent, failed & sent emails.

--
-- Other system tables to check status of emails in MSDB
--
SELECT * 
FROM msdb..sysmail_sentitems
GO
SELECT * 
FROM msdb..sysmail_unsentitems
GO
SELECT * 
FROM msdb..sysmail_faileditems
GO

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : Login failed for user. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: ]

Interesting one today:

In our lab, this error propped up in one of the environments that relies on replication heavily.

Problem Definition:

Sql Agent jobs for all the Logreader Agents & Distribution Agents are spitting out this error message while they are in an endless loop to “retry”.

Login failed for user. 
Reason: Attempting to use an NT account name with SQL Server Authentication.
[CLIENT: 'machine IP']

The error makes sense, but it does not seem actionable.

Context:

Replication is carried out between Publisher-to-Distributor-to-Subscriber (simple terms). This action is carried out by Sql Agent job running on Distributor.

Each agent, runs under the security context of a user account (Local OS or Domain account). Since some of these agents needs to talk to Publisher or Subscriber or both, they need proper privileges on all the machines to be able to carry out operations.

Usually when all the machines are in a domain, a single account runs the Agent jobs and also has permissions to connect to Subscriber & Publisher. But we have an extra option in the replication UI.

We could set up one account to run the agent on Distributor; Another to connect to Publisher or Distributor.

• For LogReader agent, it needs permissions on Distributor & Publisher
• For Distribution Agent, it needs permissions on Distributor & Subscribers

See the image below for how security is set up for LogReader Agent:

In the window above, the top section shows under what user context the agent runs on Distributor. Since it also need to connect to Publisher, in the bottom portion, it shows the account needed to connect to Publisher.

To make things easier, we have two options:

1. By Impersonating the process account:
   1. We could use the same account mentioned the top section or give a new account.
2. Use the following Sql Server Login:
   1. This new account needs to be a Sql Server Login (and not windows or domain account).

Resolution:

The line in red above was the root of the problem.

Someone selected “Use the following Sql Server Login” to “Connect to the Publisher” and provided a Domain account, rather than a Sql Server Login.

Hence the error message :

Attempting to use an NT account name with SQL Server Authentication.

Either provide correct Sql Login or choose “By Impersonating the process account”.

More Info: When you “impersonate process account” it uses the same account under which the LogReader agent runs on Distributor — hence called Process Account

 

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 »