3

I've developed an SSIS package to load files but it is behaving very strangely. I can kick off the package running start_execution on the SSISDB only when I log directly onto the server. Kicking it off from my PC will throw a warning saying the For Each File enumerator is empty. My account and the SQL SQL service account both have access to the file share where the SSIS package is pointing. Anyone else encountered a similar issue?

UPDATE:

The SSIS package will execute successfully if executed remotely through a SQL agent job.

  • I don't recall the authentication internals for the SSISDB when instantiating a package from T-SQL but based on the symptoms I would check to see if you have some kind of Kerberos issue here. You can check this with procmon with a filter set up to look for the specific path that your for each enumerator is using. Be warned that on a busy server this can get pretty heavy on the memory utilization so keep it as short as possible. – Travis Page Jan 6 '16 at 2:48
1

I would agree with the comment from Travis Page. Having just gone through this is sounds like either the Service Principal Names(SPN) are missing for the SQL Service or the file share. Ultimately you will need the "MSSQL" SPN for your SQL Server instances and the "HOST" SPN for host of the fileshare.

Some reading/blog articles to help understand troubleshoot any kerberos issues:

This blog describes the creation of SPN's using the SETSPN command line tool: http://blogs.technet.com/b/tristank/archive/2006/05/08/spns-r-fn.aspx

There is a tool published from Microsoft which will help determine the SPN's require for SQL Server. This blog talks about the tool: http://blogs.msdn.com/b/farukcelik/archive/2013/05/21/new-tool-quot-microsoft-kerberos-configuration-manager-for-sql-server-quot-is-ready-to-resolve-your-kerberos-connectivity-issues.aspx

Additionally there is this below article from Microsoft which will help troubleshoot any kerberos problems: http://blogs.technet.com/b/askds/archive/2008/05/14/troubleshooting-kerberos-authentication-problems-name-resolution-issues.aspx

  • I checked the SPNs for both the file server and SQL Server instance. The SQL Server instance is registered to the service account with two entries. One with the port number and one without. The file server has two HOST entries one complete with the domain name and one without. Both of the file server entries are registered to the correct server name. I did some additional research it seems like this is all that's necessary. Am I missing something? – DraftinBandit Jan 13 '16 at 16:49
  • Quite possibly you need SPN's for the FQDN of the SQL Instances. When I set up SPN's I will end up with 4 SPN's configured per instance for the service account. For Example: MSSQLSvc/Server:InstanceName MSSQLSvc/Server.Domain:InstanceName MSSQLSvc/Server:Port MSSQLSvc/Server.domain:Port You also need to ensure that the active directory (Server and Service Account) are configured to be trusted for delegation (technet.microsoft.com/en-us/library/cc757194(v=ws.10).aspx – Ian Chamberland Jan 14 '16 at 0:01
  • In my situation when hunting this down before, I used a network trace to look for the Kerberos error. The error I found in the network trace was "Server Not found in Kerberos Database" – Ian Chamberland Jan 14 '16 at 0:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.