Posted on Leave a comment

SQL Server 2008R2 Integration Services Access

Problem: SSIS Support Developer could not access SSIS packages in SQL Server 2008R2 Instance.

Solution:

  1. Type in “Component Services” in search bar
  2. Open Component Services
  3. Expand Component Services -> Computers -> My Computer -> DCOM Config
  4. Scroll down to MsDtsserver100 and right-click Properties

Select the Security tab

Click edit button for Launch and Activation Permissions and add permissions, same for Access Permissions

Click OK

 

Next, Right-click My Computer>Manage>Configuration>Local Users & Groups

Look for the Distributed COM Users group and add the account.

 

 

Finally, restart the SSIS service

Posted on Leave a comment

Using LogParser to get list of Sharepoint Usernames

Download and install LogParser from here:

https://www.microsoft.com/en-us/download/details.aspx?id=24659

Then create a text file query.sql containing your query


   SELECT distinct 
          replace_str(replace_str(to_lowercase(cs-username),'0#.f|ldapmember|',''),'0#.w|','') as username
     FROM C:\inetpub\logs\LogFiles\*.log
    WHERE cs-username > ''
      AND date >= '2017-01-01'

 

Then execute the following command

C:\Program Files (x86)\Log Parser 2.2\logparser file:"query.sql" -i:IISW3C -o:CSV -recurse > c:\temp\usernames.csv

(note: path to LogParser may vary on your system, it may also be handy to add it to the %PATH%)

Learn more about LogParser here:
https://technet.microsoft.com/en-us/scriptcenter/dd919274.aspx

Posted on Leave a comment

A call to ‘LogonUserW’ failed with error code: ‘1385’

Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to ‘LogonUserW’ failed with error code: ‘1385’.

1385 = Logon failure: the user has not been granted the requested logon type at this computer.

Solution:

  1. Look up the Windows user that is assigned to credential [##xp_cmdshell_proxy_account##]
  2. Run secpol.msc
  3. Navigate to “Local Policies” -> “User Rights Assignment” -> “Log on as a batch job”
  4. Add the user found from step 1 to “Log on as a batch job”
  5. Test by executing sql stored procedure: “EXEC master..xp_cmdshell ‘whoami'”

 

Troubleshooting xp_cmdshell failures

Posted on Leave a comment

Instant PRINTs

SQL “PRINT” statements getting buffered and not displaying until buffer is flushed with batch is done or gets full.

Using a RAISERROR with severity of 0 and “WITH NOWAIT” will not interrupt the batch, but will immediately display the output.

Here’s an example keeping it to one line and including a timestamp…

–for first msg in batch
DECLARE @msg nvarchar(2044) = convert(varchar(20),current_timestamp,120) + ‘ – Your First Message Here’; RAISERROR(@msg, 0, 1) WITH NOWAIT;

–for rest of msgs in batch
SET @msg = convert(varchar(20),current_timestamp,120) + ‘ – Subsequent Messages Here’; RAISERROR(@msg, 0, 1) WITH NOWAIT;

GO