Log Shipping Copy failed due to compromise security

All of sudden Log shipping copy job failing with below error
"The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you."

To resolve this issue configure the firewall - TCP port 88 and UDP port 88 are not blocked for either domain.

There is a article in MS - http://support.microsoft.com/kb/938457 explaining this error will occur when you are dealing with multi domain. 

Make sure SQL Agent services restarted after the change.

Microsoft Dynamics GP:save operation on table 'sy_users_MSTR' (45) - fix

Usually the below error will occur when you move GP database from one server to another.

"save operation on table 'sy_users_MSTR' (45)"

To fix the issue, as per Microsoft the GP databases owner should be "DYNSA" not sa or any other user.

Ref : https://support.microsoft.com/en-us/kb/860468

Copy Folder Structure from one server to other using Robocopy

C:\Windows\System32>Robocopy.exe "source" "destination" /e /xf *
Switch /e will copy empty directories and /xf * will exclude files.

To exclude certain files, such as txt files then you could do
C:\Windows\System32>Robocopy.exe "source" "destination" /e /xf *.txt

SQL Server Audit

How to capture SQL database activities using SQL Audit.

<This functionality is not available in SQL Standard Edition>

-> Create Server level Audit.

EXEC sp_MSForEachDB 'If ''?'' not in ( ''master'',''msdb'',''model'',''tempdb'')
Begin
CREATE SERVER AUDIT [Audit-?]
TO FILE
( FILEPATH = N''I:\Test''
  ,MAXSIZE = 0 MB
  ,MAX_ROLLOVER_FILES = 2147483647
  ,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT [Audit-?] WITH (STATE = ON)
END'

-> Create Database Audit Specification.

EXEC sp_MSForEachDB 'If ''?''  not in ( ''master'',''msdb'',''model'',''tempdb'')
Begin
USE [?];
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-?]
FOR SERVER AUDIT [Audit-?]
ADD (DELETE ON DATABASE::[?] BY [public]),
ADD (EXECUTE ON DATABASE::[?] BY [public]),
ADD (INSERT ON DATABASE::[?] BY [public]),
ADD (SELECT ON DATABASE::[?] BY [public]),
ADD (UPDATE ON DATABASE::[?] BY [public]);
ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-?]
WITH (STATE = ON)
End'

-> To read the Audit file.
SELECT * FROM sys.fn_get_audit_file ('I:\test\<filename>.sqlaudit',default,default);
GO

Unable to connect SSIS from remote

Access Denied while connecting SSIS from remote.

Error:
------------------------------------------------
Cannot connect to TESTSERVER1.
------------------------------------------------
Connecting to the Integration Services service on the computer "TESTSERVER1" failed with the following error: "Access is denied".
This error occurs when the computer has not been configured to allow remote connections through DCOM, or the user does not have permission to access the SQL Server Integration Services service through DCOM.
-----------------------------------------------
To fix this error and connect the SSIS from remote, try below steps.

Add the user account to the Distributed COM Users group. To do this, follow these steps:

1. Click Start, click Run, type lusrmgr.msc, and then click OK.
2. In the Local Users and Groups dialog box, click Groups, and then double-click Distributed COM Users.
3. In the Distributed COM Users Properties dialog box, click Add.
4. In the Select Users dialog box, type the user name under Enter the object names to select, and then click OK two times. 

  Grant the appropriate permissions for the MsDtsServer DCOM application. To do this, follow these steps:

1. Click Start, click Run, type dcomcnfg, and then click OK.
2. In the Component Services dialog box, expand Component Services, expand Computers, and then expand My Computer.
3. Expand DCOM Config, right-click the MsDtsServer object, and then click Properties.
4. In the MsDtsServer (SQL Server 2005) or MsDtsServer100 (SQL server 2008) Properties dialog box, click the Security tab.
5. Under Launch and Activation Permissions, click Customize, and then click Edit.
6. In the Launch Permission dialog box, click Add.
7. In the Select Users or Groups dialog box, type the user name under Enter the object names to select, and then click OK.
8. In the Launch Permission dialog box, click the user name under Group or user names.
9. Under Permissions for UserName, click to select the Allow check box for the following permissions:
   • Local Launch
   • Remote Launch
   • Local Activation
   • Remote Activation
   Note UserName is a placeholder for the user name that you clicked in step h.
10. Click OK.
11. In the MsDtsServer (SQL Server 2005) or MsDtsServer100 (SQL server 2008) Properties dialog box, click Customize under Access Permissions, and then click Edit.
12. In the Access Permission dialog box, click Add.
13. In the Select Users or Groups dialog box, type the user name under Enter the object names to select, and then click OK.
14. In the Access Permission dialog box, click the user name under Group or user names.
15. Under Permissions for UserName, click to select the Allow check box for the following permissions:
    • Local Access
    • Remote Access
16. Click OK two times.

 Restart the SQL Server Integration Services service.

SQL Server trace for capturing connection info

-- variables
declare @rc int
declare @TraceID int
declare @maxFileSize bigint
declare @fileName nvarchar(128)
declare @on bit
set @maxFileSize = 200
set @fileName = N'C:\Trace'
set @on = 1

-- create trace
exec @rc = sp_trace_create @TraceID output, 2, @fileName, @maxFileSize, NULL
-- if error end process
if (@rc != 0) goto error

-- set the events
-- logins
exec sp_trace_setevent @TraceID, 14, 3, @on --DatabaseID
exec sp_trace_setevent @TraceID, 14, 6, @on -- NTUsername
exec sp_trace_setevent @TraceID, 14, 7, @on -- NTDomainname
exec sp_trace_setevent @TraceID, 14, 8, @on -- Hostname
exec sp_trace_setevent @TraceID, 14, 10, @on -- Applicationname
exec sp_trace_setevent @TraceID, 14, 11, @on -- Loginname
exec sp_trace_setevent @TraceID, 14, 12, @on -- SPID
exec sp_trace_setevent @TraceID, 14, 13, @on -- Duration
exec sp_trace_setevent @TraceID, 14, 14, @on -- Starttime
exec sp_trace_setevent @TraceID, 14, 15, @on -- Endtime
exec sp_trace_setevent @TraceID, 14, 21, @on -- Eventsubclass
exec sp_trace_setevent @TraceID, 14, 26, @on -- Servername
exec sp_trace_setevent @TraceID, 14, 35, @on -- Databasename
exec sp_trace_setevent @TraceID, 14, 40, @on -- DBusername
exec sp_trace_setevent @TraceID, 14, 51, @on -- Eventsequence
exec sp_trace_setevent @TraceID, 14, 64, @on -- Sessionloginname

-- set Filters
exec sp_trace_setfilter @TraceID, 11, 0, 6, 'testuser'

-- start the trace
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
-- error
error:
select ErrorCode=@rc
-- exit
finish:
go

--******************************************************--

-- to view the trace information
select * from :: fn_trace_getinfo(default)
-- to stop and delete the trace
exec sp_trace_setstatus @traceid, @status = 0 -- 0 stop
exec sp_trace_setstatus @traceid, @status = 2 -- 2 delete

-- load the trace into temp table
use testdb
go
select * into temp_trace
from fn_trace_gettable('C:\Trace.trc', default);
go

Specified credentials for the SQL Server/SQL Server Agent service are not valid

SQL 2008 R2 Cluster installation failed while updating service account details
Microsoft.SqlServer.Configuration.SqlEngine.ValidationException: The specified credentials for the SQL Server service are not valid. To continue, provide a valid account and password for the SQL Server service.

Below message will be logged in details
Sco.User.LookupADEntry - Attempting to find user account ABC\xxxx
Slp: Sco: Attempting to check if container 'WinNT://ABC' of user account exists
Slp: UserSecurity.ValidateCredentials -- Exception caught and ignored, exception is Access is denied.
Slp: UserSecurity.ValidateCredentials -- user validation failed

This error is due to lack of permission in AD.
The account requires Create Computer objects and Read All Properties permissions in the container that is used for computer accounts in the AD.

1. On a domain controller, click Start, click Administrative Tools, and then click Active Directory Users and Computers. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
2. On the View menu, make sure that Advanced Features is selected.
   When Advanced Features is selected, you can see the Security tab in the properties of accounts (objects) in Active Directory Users and Computers.
3. Right-click the default Computers container or the default container in which computer accounts are created in your domain, and then click Properties. Computers is located in Active Directory Users and Computers/domain node/Computers.
4. On the Security tab, click Advanced.
5. Click Add, type the name of the account and then click OK.
6. In the Permission Entry for container dialog box, locate the Create Computer objects and Read All Properties permissions, and make sure that the Allow check box is selected for each one.