BUG: SQL authentication failure

jmeyer · August 31, 2016 9:41AM

v12.0.24.3012 Professional:
Source database uses Windows authentication, target database uses SQL Server authentication. As soon as I select an existing project I am receiving notifications from SQL Server (target) for authentication failures:"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.". It seems that DataCompare tries to first authenticate against the target database with integrated security and then switches over to the configured SQL Server authentication. The autnetication errors will then also occur

This behavior is not present in v11. SQLCompare shows the same issue, denoting a bug in the underlying common component.

Mark J · September 1, 2016 4:59AM

We've had a look at this problem, but haven't been able to reproduce it locally - we'll keep investigating and ask if we can think of any more details that would be helpful

jmeyer · September 1, 2016 10:18AM

Mark:

The target SQL Server instance is located in a different domain, maybe that's the critical difference. Our SQL Server instance then creates to login failure warnings:

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: x.x.x.x].

Login Error 18452: DESCRIPTION: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: x.x.x.x]
COMMENT: Login failed for user . Reason: Not associated with a trusted SQL Server connection.

Andrew F · September 26, 2016 6:19AM

We are still failing to reproduce this issue here even between different domains, as you suggested. Are you using any non-standard connection strings to connect to either the source or the target database?

jmeyer · September 28, 2016 9:51AM

Update based on v12.0.30.3199

Computer is in domain A, the SQL Server instance is in domain B. Domains do not trust each other, connections to the SQL Server instance in domain B will happen via SQL authentication. Created a new comparison project comparing the "model" database to itself, thus both source and destination connection are set up with SQL Server authentication. Saved the project and then closed and reopened the SQL Data compare instance. the comparison project comes up and right away I am receiving two identical authentication errors from the SQL Server instance:
DESCRIPTION: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 10.10.130.56]
COMMENT: Login failed for user . Reason: Not associated with a trusted SQL Server connection.

I have to assume that each authentication error represents one connection attempt each for the source and destination database.

clicking the database refresh button in the "Data Sources" tab will not result in authentication errors. Closing and re-opening the project will again result in two authentication errors. so, there is something going on as part of the initial process when you open a project.

the project file is posted below:

&lt;?xml version="1.0" encoding="utf-16" standalone="yes"?&gt;
&lt;!--
SQL Data Compare
SQL Data Compare
Version:12.0.30.3199--&gt;
&lt;Project version="3" type="SQLComparisonToolsProject"&gt;
  &lt;DataSource1 version="3" type="LiveDatabaseSource"&gt;
    &lt;ServerName&gt;QSQL08OCR&lt;/ServerName&gt;
    &lt;DatabaseName&gt;model&lt;/DatabaseName&gt;
    &lt;Username&gt;meyer&lt;/Username&gt;
    &lt;SavePassword&gt;True&lt;/SavePassword&gt;
    &lt;Password encrypted="1"&gt;LsMNNZvlguq=&lt;/Password&gt;
    &lt;ScriptFolderLocation /&gt;
    &lt;MigrationsFolderLocation /&gt;
    &lt;IntegratedSecurity&gt;False&lt;/IntegratedSecurity&gt;
  &lt;/DataSource1&gt;
  &lt;DataSource2 version="3" type="LiveDatabaseSource"&gt;
    &lt;ServerName&gt;QSQL08OCR&lt;/ServerName&gt;
    &lt;DatabaseName&gt;model&lt;/DatabaseName&gt;
    &lt;Username&gt;meyer&lt;/Username&gt;
    &lt;SavePassword&gt;True&lt;/SavePassword&gt;
    &lt;Password encrypted="1"&gt;LsMNNZvlguq=&lt;/Password&gt;
    &lt;ScriptFolderLocation /&gt;
    &lt;MigrationsFolderLocation /&gt;
    &lt;IntegratedSecurity&gt;False&lt;/IntegratedSecurity&gt;
  &lt;/DataSource2&gt;
  &lt;LastCompared&gt;09/28/2016 09:41:07&lt;/LastCompared&gt;
  &lt;Options&gt;Default, CaseSensitiveObjectDefinition&lt;/Options&gt;
  &lt;InRecycleBin&gt;False&lt;/InRecycleBin&gt;
  &lt;Direction&gt;0&lt;/Direction&gt;
  &lt;ProjectFilter version="1" type="DifferenceFilter"&gt;
    &lt;FilterCaseSensitive&gt;False&lt;/FilterCaseSensitive&gt;
    &lt;Filters version="1"&gt;
      &lt;None version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/None&gt;
      &lt;Assembly version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/Assembly&gt;
      &lt;AsymmetricKey version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/AsymmetricKey&gt;
      &lt;Certificate version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/Certificate&gt;
      &lt;Contract version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/Contract&gt;
      &lt;DdlTrigger version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/DdlTrigger&gt;
      &lt;Default version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/Default&gt;
      &lt;ExtendedProperty version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/ExtendedProperty&gt;
      &lt;EventNotification version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/EventNotification&gt;
      &lt;FullTextCatalog version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/FullTextCatalog&gt;
      &lt;FullTextStoplist version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/FullTextStoplist&gt;
      &lt;Function version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/Function&gt;
      &lt;MessageType version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/MessageType&gt;
      &lt;PartitionFunction version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/PartitionFunction&gt;
      &lt;PartitionScheme version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/PartitionScheme&gt;
      &lt;Queue version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/Queue&gt;
      &lt;Role version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/Role&gt;
      &lt;Route version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/Route&gt;
      &lt;Rule version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/Rule&gt;
      &lt;Schema version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/Schema&gt;
      &lt;SearchPropertyList version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/SearchPropertyList&gt;
      &lt;SecurityPolicy version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/SecurityPolicy&gt;
      &lt;Sequence version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/Sequence&gt;
      &lt;Service version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/Service&gt;
      &lt;ServiceBinding version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/ServiceBinding&gt;
      &lt;StoredProcedure version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/StoredProcedure&gt;
      &lt;SymmetricKey version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/SymmetricKey&gt;
      &lt;Synonym version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/Synonym&gt;
      &lt;Table version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/Table&gt;
      &lt;User version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/User&gt;
      &lt;UserDefinedType version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/UserDefinedType&gt;
      &lt;View version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/View&gt;
      &lt;XmlSchemaCollection version="1"&gt;
        &lt;Include&gt;True&lt;/Include&gt;
        &lt;Expression&gt;TRUE&lt;/Expression&gt;
      &lt;/XmlSchemaCollection&gt;
    &lt;/Filters&gt;
  &lt;/ProjectFilter&gt;
  &lt;ProjectFilterName /&gt;
  &lt;UserNote /&gt;
  &lt;SelectedSyncObjects version="1" type="SelectedSyncObjects"&gt;
    &lt;Schemas type="ListString" version="2" /&gt;
    &lt;Grouping type="ListByte" version="2"&gt;
      &lt;value type="Byte"&gt;0&lt;/value&gt;
      &lt;value type="Byte"&gt;0&lt;/value&gt;
      &lt;value type="Byte"&gt;0&lt;/value&gt;
      &lt;value type="Byte"&gt;0&lt;/value&gt;
      &lt;value type="Byte"&gt;0&lt;/value&gt;
      &lt;value type="Byte"&gt;0&lt;/value&gt;
    &lt;/Grouping&gt;
    &lt;SelectAll&gt;False&lt;/SelectAll&gt;
  &lt;/SelectedSyncObjects&gt;
  &lt;SCGroupingStyle&gt;0&lt;/SCGroupingStyle&gt;
  &lt;SQLOptions&gt;266&lt;/SQLOptions&gt;
  &lt;MappingOptions&gt;81&lt;/MappingOptions&gt;
  &lt;ComparisonOptions&gt;35&lt;/ComparisonOptions&gt;
  &lt;TableActions type="ArrayList" version="1" /&gt;
  &lt;SessionSettings&gt;14&lt;/SessionSettings&gt;
  &lt;DCGroupingStyle&gt;0&lt;/DCGroupingStyle&gt;
  &lt;SC_DeploymentOptions version="1" type="SC_DeploymentOptions"&gt;
    &lt;BackupOptions version="1" type="BackupOptions"&gt;
      &lt;BackupProvider&gt;Native&lt;/BackupProvider&gt;
      &lt;TypeOfBackup&gt;Full&lt;/TypeOfBackup&gt;
      &lt;Folder /&gt;
      &lt;Filename /&gt;
      &lt;SqbLicenseType&gt;None&lt;/SqbLicenseType&gt;
      &lt;SqbVersion&gt;0&lt;/SqbVersion&gt;
      &lt;DefaultNativeFolder /&gt;
      &lt;DefaultSqbFolder /&gt;
      &lt;Password encrypted="1" /&gt;
      &lt;NameFileAutomatically&gt;False&lt;/NameFileAutomatically&gt;
      &lt;OverwriteIfExists&gt;False&lt;/OverwriteIfExists&gt;
      &lt;CompressionLevel&gt;0&lt;/CompressionLevel&gt;
      &lt;EncryptionLevel&gt;None&lt;/EncryptionLevel&gt;
      &lt;ThreadCount&gt;0&lt;/ThreadCount&gt;
      &lt;BackupEnabled&gt;False&lt;/BackupEnabled&gt;
    &lt;/BackupOptions&gt;
  &lt;/SC_DeploymentOptions&gt;
&lt;/Project&gt;

David E · September 29, 2016 5:11AM

Hi,

Thanks for the extra information - its very useful in helping us track down the exact location of the bug. To confirm these errors are appearing only on load of the project dialog and not when you press the refresh icon next to the database dropdown? Where do these errors appear? Are they shown in the validation section at the bottom of the form or do they produce a popup?

I suspect this is related to the server dropdown population rather than the database lister but I'm going to do some more work locally to try and narrow this down.

jmeyer · September 29, 2016 9:27AM

David:

Yes, the error messages are only generated one time when opening the project in the project dialog. The errors themselves are reported back to us from the SQL Server instances as we set up alert notification emails for failed login attempts. So, SQL DataCompare (and SQLCompare as well) actually not not produce any error messages themselves. If I press the refresh button for the database drop-down box or proceed with a comparison then no error messages are being generated. Below si the SQL code to set up notification for login error 18452.

USE &#91;msdb&#93;
GO

IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'QSQL08OCR: Login Error 18452')
EXEC msdb.dbo.sp_delete_alert @name=N'Servername: Login Error 18452'
GO

EXEC msdb.dbo.sp_add_alert @name=N'Servername: Login Error 18452', 
		@message_id=18452, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@notification_message=N'Login failed for user . Reason: Not associated with a trusted SQL Server connection.', 
		@category_name=N'SQL Server Agent Alerts', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_update_notification @alert_name=N'Servername: Login Error 18452', @operator_name=N'SQL Server Agent Alert Notification Group', @notification_method = 1
GO

sam.blackburn · October 10, 2016 5:34AM

We think we've fixed this bug (SC-8959) in the latest Frequent Update version, 12.0.32.3340 - does that version work for you?

jmeyer · October 21, 2016 5:42PM

Sam:

It looks like this is fixed now, currently using v12.0.33.3389

Thanks,
Jens

BUG: SQL authentication failure

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk