Here are some we’ve encountered with resolutions.
Failed to decrypt protected XML node ... "Key not valid for use in specified state."
The creator of the package is someone else than the process owner of the account running it.
If you create the package and run from within SQL the executing account would be the service account.
https://www.mssqltips.com/sqlservertip/2091/securing-your-ssis-packages-using-package-protection-level/
PowerShell error:
Remoting needs to be set up on [ServerName]
Try running winrm quickconfig on remote server.
Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation. All rights reserved. DOS;winrm quickconfig WinRM already is set up to receive requests on this machine. WinRM is not set up to allow remote access to this machine for management. The following changes must be made: Create a WinRM listener on HTTP://* to accept WS-Man requests to any IP on this machine. Enable the WinRM firewall exception. Make these changes [y/n]? y WinRM has been updated for remote management. Created a WinRM listener on HTTP://* to accept WS-Man requests to any IP on this machine. WinRM firewall exception enabled.
We were trying to connect a remote SQL publisher to a local subscriber and on the subscriber get these errors, found in the SQL Errorlog.
Date 12/8/2016 6:52:42 AM Log SQL Server (Current - 12/7/2016 6:03:00 PM) Source Logon Message Error: 17806, Severity: 20, State: 14. ---- Date 12/8/2016 6:52:42 AM Log SQL Server (Current - 12/7/2016 6:03:00 PM) Source Logon Message SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed [CLIENT: 172.31.5.158] ---- Date 12/8/2016 6:52:42 AM Log SQL Server (Current - 12/7/2016 6:03:00 PM) Source Logon Message Error: 18452, Severity: 14, State: 1. ---- Date 12/8/2016 6:52:42 AM Log SQL Server (Current - 12/7/2016 6:03:00 PM) Source Logon Message Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: xxx.xx.x.xxx] ----
However, the SQL login was coming from a domain that already HAD replication running on another database.
It turned out to be an incorrect login name, rather than an untrusted domain.
Never trust an error message.
Msg 11520, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement ‘EXECUTE master.dbo.xp_sqlagent_is_starting @retval OUTPUT’ in procedure ‘sp_is_sqlagent_starting’ invokes an extended stored procedure.
In SQL 2012 sp_help_job has changed. See this solution:
Issue:In SQL 2012, stored procedures that call any other stored procedures or extended stored procedures that return result sets should specify WITH RESULT SETSSQL Agent’s stored procedure sp_help_job – returns single resultset if no parameters are passed to stored procedure. – returns 3 resultsets if job_id or job_name is specified. MSDN linkWhen RESULTSET is not described,…
RESTORE filelistonly FROM DISK = N'E:\MSSQL\My2012Backup'
Ooops. Trying to restore a more current database backup on an older server got me this error.
Msg 3241, Level 16, State 13, Line 2 The media family on device 'E:\MSSQL\My2012backup' is incorrectly formed. SQL Server cannot process this media family. Msg 3013, Level 16, State 1, Line 2 RESTORE FILELIST is terminating abnormally.
Run the restore command on a newer version of SQL fixes the error.
DESCRIPTION: SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. [CLIENT: 10.12.23.345].
This can be related to an AD login account expiring and the user leaving SSMS or something running on their PC. The account eventually locks out and SQL sees this message until the user resets their password.
Here’s my script to fix this error. This occurs when I restore a database to another server and want to remove the source server users.
DECLARE @vcSchemaName VARCHAR(128); SET @vcSchemaName = 'schema name goes here' SELECT s.name, 'ALTER AUTHORIZATION ON SCHEMA::[' + @vcSchemaName + '] TO dbo;' FROM sys.schemas s WHERE s.principal_id = USER_ID(@vcSchemaName
Unless you are an ‘sa’ you probably will get this error.
Fix it by adding this setting to the registry.
Value Data
DisallowAdHocAccess 0
—
— Quickly check your registry settings running this on SQL 2005+ servers
EXEC master..xp_regenumvalues
‘HKEY_LOCAL_MACHINE’,
‘SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\Providers\MSDASQL’
— or on SQL 2000
EXEC master..xp_regenumvalues
‘HKEY_LOCAL_MACHINE’,
‘SOFTWARE\Microsoft\MSSQLServer\Providers\MSDASQL\’
Got this when I ran my sp__updatestats procedure in a SQL Agent jobstep, but it worked in Query Analyzer. The reason? The default settings are probably different in QA rather than external connections similar to SQL Agent.
Msg 1934, Sev 16: UPDATE STATISTICS failed because the following SET options have incorrect settings: 'ARITHABORT'. [SQLSTATE 42000]
The solution was to recreate the procedure that ran UPDATE STATISTICS using a SET command within the procedure.
CREATE PROCEDURE dbo.sp__UpdateStats
AS
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
...
We started to get this problem after I added a computed column to a table.
PROBLEM: MSDTC seems to be working. e.g. Linked queries to servers or in this case a text file can be queried etc. while running individual commands in Query Analyzer, but fail running the entire procedure.
Not caring the whys of it, what is wrong with DTC that we can fix?
For Windows 2003 SP1, see these instructions. Basically don’t use Local System account. Try this account and search for the local account login of NetworkService. When it comes up there is something in the password field, ooops. Seems to work if we clear both password textboxes.
After your changes simply bounce the MSDTC service and run your SQL query again. This time it should work.