SQLServer security

by

Bulk insert fails with error 4861 Cannot bulk load because the file could not be opened

When you do bulk insert in SQL Server it may fail with below error because of double hop .

Error :

Msg 4861, Level 16, State 1, Line 3

Cannot bulk load because the file “\\path\” could not be opened. Operating system error code 5(failed to retrieve text for this error. Reason: 15105).

Steps to fix the above error

1. Connect to SQL Server using SSMS (With account you run bulk insert) and execute below query and check if it is using Kerberos authentication

select net_transport,auth_scheme from sys.dm_exec_connections where session_id=@@spid

2. If the session is not using Kerberos authentication then fix the SPN issues (startup account of SQL Server should have read and write SPN permissions). Account which is running bulk inser should have read SPN permission. Setpn exe or adsiedit can be used to add or display all the SPN’s. If the SPn’s are registered properly and still connection fails to NTLM then get the output of SSPIClient.exe and verify why Kerberos authentication fails.

3. Make sure the account which is running bulk insert is trusted for delegation in Active Directory.

4. Account which is running bulk insert should have access to the shared directory in which BCP files are placed.

Reference  BULK INSERT (Transact-SQL) 

http://msdn.microsoft.com/en-us/library/ms188365.aspx

Security Account Delegation (Impersonation)

If a SQL Server user is logged in using Windows Authentication, the user can read only the files accessible to the user account, independent of the security profile of the SQL Server process.

When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.

To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.

For more information about this and other security considerations for using BULK INSERT, see Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK…) (SQL Server).

by

SSIS package fails when executed as job using proxy account

Issue

The SQL server Integration package which transfers the data from data source like excel to SQL Server database fails when executed from SQL Agent job using proxy account

ERROR

Date                      4/25/2013 4:16:34 PM

Log                         Job History (SSISTest)

Step ID                 1

Server                   Myserver\SQL2008STD

Job Name                            SSISTest

Step Name                         SSISJob1

Duration                              00:00:01

Sql Severity                        0

Sql Message ID                 0

Operator Emailed                           

Operator Net sent                          

Operator Paged                               

Retries Attempted                          0

Message

Executed as user: MyDomain\MyUser1. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.4276.0 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  4:16:34 PM  Info: 2013-04-25 16:16:34.81     Code: 0x4004300A     Source: Data Flow Task to move data from MS Excel to SQL server database SSIS.Pipeline     Description: Validation phase is beginning.  End Info  Error: 2013-04-25 16:16:35.04     Code: 0xC0202009     Source: Package Connection manager “Excel Connection Manager 1”     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: “Microsoft Access Database Engine”  Hresult: 0x80004005  Description: “The Microsoft Access database engine cannot open or write to the file ”. It is already opened exclusively by another user, or you need permission to view and write its data.”.  End Error  Error: 2013-04-25 16:16:35.05     Code: 0xC020801C     Source: Data Flow Task to move data from MS Excel to SQL server database Excel Source [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager 1” failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2013-04-25 16:16:35.07     Code: 0xC0047017     Source: Data Flow Task to move data from MS Excel to SQL server database SSIS.Pipeline     Description: component “Excel Source” (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2013-04-25 16:16:35.07     Code: 0xC004700C     Source: Data Flow Task to move data from MS Excel to SQL server database SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-04-25 16:16:35.09     Code: 0xC0024107     Source: Data Flow Task to move data from MS Excel to SQL server database      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  4:16:34 PM  Finished: 4:16:35 PM  Elapsed:  0.764 seconds.  Process Exit Code 1.  The step failed.

Cause:

Missing permission on PROFILE directory of SQL server Agent service account for Proxy account .

BufferTEMPstorage path and BlobTempstoragepath are defaulted to TEMP and TMP environment variables for SQL Server agent start up account by default.

When you use proxy account to execute a package then proxy account should have access to temp and TMP folder of SQL Server agents start up account profile.

TEMP=C:\Users\StartupaccountofSQLAgent\AppData\Local\Temp

TMP=C:\Users\ StartupaccountofSQLAgent\AppData\Local\Temp

If your start up account of SQL Server agent is Local service or Network service then proxy account should have permission for TMP and TEMP folder located under C:\Windows\ServiceProfiles for Local service and Network service

If the proxy account doesn’t have access to this location SSIS job would produce the below error:

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted

04/08/2013 11:26:19,ExcelTC,Error,0,UKLONDT642483,ExcelTC,(Job outcome),,The job failed.  The Job was invoked by User MyDomain\MyUser1.  The last step to run was step 1 (ExcelTC\Package).,00:00:01,0,0,,,,0

04/08/2013 11:26:19,ExcelTC,Error,1,UKLONDT642483,ExcelTC,ExcelTC\Package,,Executed as user: MyDomain\MyUser1. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.   

Started:  11:26:19  Info: 2013-04-08 11:26:19.94     Code: 0x4004300A     Source: Data Flow Task SSIS.Pipeline     Description: Validation phase is beginning.  End Info  Error: 2013-04-08 11:26:19.98     Code: 0xC0202009     Source: Package Connection manager “Excel Connection Manager”     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: “Microsoft Office Access Database Engine”  Hresult: 0x80004005  Description: “Unspecified error”.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC020801C     Source: Data Flow Task Excel Source [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC0047017     Source: Data Flow Task SSIS.Pipeline     Description: component “Excel Source” (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC004700C     Source: Data Flow Task SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC0024107     Source: Data Flow Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:26:19  Finished: 11:26:19  Elapsed:  0.405 seconds.  Process Exit Code 1.  The step failed.,00:00:01,0,0,,,,0

04/08/2013 11:10:11,ExcelTC,Error,0,UKLONDT642483,ExcelTC,(Job outcome),,The job failed.  The Job was invoked by User MyDomain\MyUser1.  The last step to run was step 1 (ExcelTC\Package).,00:00:01,0,0,,,,0

04/08/2013 11:10:11,ExcelTC,Error,1,UKLONDT642483,ExcelTC,ExcelTC\Package,,Executed as user: MyDomain\MyUser1. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  11:10:12  Info: 2013-04-08 11:10:12.65     Code: 0x4004300A     Source: Data Flow Task SSIS.Pipeline     Description: Validation phase is beginning.  End Info  Error: 2013-04-08 11:10:12.78     Code: 0xC0202009     Source: Package Connection manager “Excel Connection Manager”     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: “Microsoft Office Access Database Engine”  Hresult: 0x80004005  Description: “Unspecified error”.  End Error  Error: 2013-04-08 11:10:12.78     Code: 0xC020801C     Source: Data Flow Task Excel Source [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2013-04-08 11:10:12.79     Code: 0xC0047017     Source: Data Flow Task SSIS.Pipeline     Description: component “Excel Source” (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2013-04-08 11:10:12.79     Code: 0xC004700C     Source: Data Flow Task SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-04-08 11:10:12.79     Code: 0xC0024107     Source: Data Flow Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:10:12  Finished: 11:10:12  Elapsed:  0.593 seconds.  Process Exit Code 1.  The step failed.,00:00:01,0,0,,,,0

by

Builtin\Administrators cannot login in to SQL Server

 

If you add a windows login to administrators group and even though administrators group is part of SQL Server login you may not be able to login in to SQL Server using the windows login.

Similarly

1. You remove a windows login from a windows group and assume windows group is part of SQL server login had deny on certain objects in database.

2. Now you add the windows login explicitly to SQL Server logins and grant permissions on objects which had deny for windows group. Still the login will not be able to access the objects which have deny for the group and may raise 229  similar to one below

{The SELECT permission was denied on the object ”, database mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229) }

 

This can happen when SQL is creating the logintoken from LSACache.  (LSA Cache is not refreshed after the Admin2 is added to Administrators group)

1. Disable LSACache on the machine. Steps are included in http://support.microsoft.com/kb/946358.
2. Restart the machine.
3. Connect using the problematic login and try again.

by

How to audit SQLServer logins?

We can audit SQL Server logins using multiple ways like running a profiler, Logon triggers and creating a audit.

 

Below script will create a audit to capture the login events. We can capture the login in event logs or file.

Below is example to capture login events in file.  Replace D:\Audit\ in below script to a valid folder

 

USE [master]
GO

CREATE SERVER AUDIT [TestAudit]
TO FILE 
(	FILEPATH = N'D:\CASEDATA\'
	,MAXSIZE = 0 MB
	,MAX_ROLLOVER_FILES = 2147483647
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = SHUTDOWN
	,AUDIT_GUID = 'b305ddf6-3487-4c77-afc2-fc8cfc357abc'
)
GO

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20130205-205300]
FOR SERVER AUDIT [TestAudit]
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = OFF)
GO