Bcp Export Login Failed for User Nt Authority/Anonymous Logon

BCP Export Login Failed for User NT Authority/Anonymous Logon

You're passing off a command to an external application.

This external application doesn't really know you, or where your command came from. Hence the ANONYMOUS LOGON message.

You need to tell the command who to execute as. Have a look at BCP in BoL (http://msdn.microsoft.com/en-us/library/ms162802.aspx) and you'll spot a few parameters that you might wish to add:

-T
Specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. The security credentials of the network user, login_id, and password are not required. If –T is not specified, you need to specify –U and –P to successfully log in.

-U login_id
Specifies the login ID used to connect to SQL Server.

-P password
Specifies the password for the login ID. If this option is not used, the bcp command prompts for a password. If this option is used at the end of the command prompt without a password, bcp uses the default password (NULL).

SQL Server returns error Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. in Windows application

If your issue is with linked servers, you need to look at a few things.

First, your users need to have delegation enabled and if the only thing that's changed, it'l likely they do. Otherwise you can uncheck the "Account is sensitive and cannot be delegated" checkbox is the user properties in AD.

Second, your service account(s) must be trusted for delegation. Since you recently changed your service account I suspect this is the culprit. (http://technet.microsoft.com/en-us/library/cc739474(v=ws.10).aspx)

You mentioned that you might have some SPN issues, so be sure to set the SPN for both endpoints, otherwise you will not be able to see the delegation tab in AD. Also make sure you're in advanced view in "Active Directory Users and Computers."

If you still do not see the delegation tab, even after correcting your SPN, make sure your domain not in 2000 mode. If it is, you can "raise domain function level."

At this point, you can now mark the account as trusted for delegation:

In the details pane, right-click the user you want to be trusted for
delegation, and click Properties.

Click the Delegation tab, select the Account is trusted for delegation
check box, and then click OK.

Finally you will also need to set all the machines as trusted for delegation.

Once you've done this, reconnect to your sql server and test your liked servers. They should work.

DDL Trigger For Logon Insert data to a table

Must change trigger to below code:

CREATE TRIGGER [DDLForLogin] ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON 
AS BEGIN
    DECLARE @data XML
    SET @data = EVENTDATA()

    DECLARE @IsPooled int
    SET @IsPooled = @data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'int')

    IF EXISTS(SELECT * FROM sys.Databases WHERE NAME = 'DatabaseMaintenance')AND (@IsPooled=0) Begin
        insert into DatabaseMaintenance.dbo.Login (UserName, HostName, ApplicationName, EventDataValue)
        values (ORIGINAL_LOGIN(), HOST_Name(), APP_NAME(),@Data)
    END
END;

---

Related Topics