Command authenticates differently if FQDN or IP is specified for file share

0

This is a slightly complicated setup. I have 2 domains DomainA and DomainB, there is a trust between the two

When I run the following from the SQL server in domainA to the SQL server on DomainB

DECLARE @DatabaseFolder varchar(4000)
DECLARE @DeleteDate DATETIME = GETDATE()
set @DatabaseFolder=N'\\sql.DomainB.local\copyonly\'
--Delete old backup files.
EXEC master.sys.xp_delete_file 0,@DatabaseFolder,'BAK', @DeleteDate,0;

It says it cannot find the file specified. and on teh DomainB SQL server it complains that the username and password of a user was incorrect.

An account failed to log on.

Subject:
    Security ID:        NULL SID
    Account Name:       -
    Account Domain:     -
    Logon ID:       0x0

Logon Type:         3

Account For Which Logon Failed:
    Security ID:        NULL SID
    Account Name:       jane.doe
    Account Domain:     DomainB

Failure Information:
    Failure Reason:     Unknown user name or bad password.
    Status:         0xC000006D
    Sub Status:     0xC000006A

Process Information:
    Caller Process ID:  0x0
    Caller Process Name:    -

Network Information:
    Workstation Name:   SQLServerName.DomainA.Local
    Source Network Address: 10.0.2.211
    Source Port:        64747

If I change it up and use the IP of SQL server in DomainB, it works

DECLARE @DatabaseFolder varchar(4000)
DECLARE @DeleteDate DATETIME = GETDATE()
set @DatabaseFolder=N'\\192.168.77.20\copyonly\'
--Delete old backup files.
EXEC master.sys.xp_delete_file 0,@DatabaseFolder,'BAK', @DeleteDate,0;

And I get a login succeeded msg in the security event logs

Special privileges assigned to new logon.

Subject:
    Security ID:        DomainA\SQLServerDomainA
    Account Name:       SQLServerDomainA$
    Account Domain:     DomainA
    Logon ID:       0x37F83A1

Privileges:     SeSecurityPrivilege
            SeBackupPrivilege
            SeRestorePrivilege
            SeTakeOwnershipPrivilege
            SeDebugPrivilege
            SeSystemEnvironmentPrivilege
            SeLoadDriverPrivilege
            SeImpersonatePrivilege
            SeDelegateSessionUserImpersonatePrivilege

I don't get why it is using different accounts if I use FQDN vs IP. And I have no idea where it is even configured to use a user account to try and login when I use FQDN anyway. That user account does exist in DomainB.

I checked the trust and it is working, conditional forwarders are working, DNS resolves without issue. In the firewall there is an Any<->Any rule between the two subnets, no packets are being dropped.

sql-server
asked on Stack Overflow Mar 16, 2020 by Gummby8

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0