Post reply

SQL Server Destination required to use Windows Authentication

  • March 28, 2011 at 1:33 am

    #145327

    Hi guys,

    I have a Data Flow Task that contains an SQL Server Destination. The package is being run in the local machine.

    The package runs successfully when the OLEDB Connection Manager used by the SQL Server Destination uses Windows Authentication.

    But it fails when I switch to SQL Authentication. The error is about the "Create Global objects" permission. From what I know, this permission is applied to the user that runs the package and not the user who connects to SQL Server.

    Is Windows Authentication really required for SQL Server Destination?

    Thanks guys.

  • March 28, 2011 at 2:52 am

    #1304217

    But it fails when I switch to SQL Authentication.

    not sure if you referring this switch within DTS/SSIS.

    If that is the case then for SQL server windows authentication and sql authentication are 2 different things. If you have windows authentication, it doesnt mean you have sql authentication as well and vice versa.

    ----------
    Ashish

  • March 28, 2011 at 4:23 am

    #1304256

    It should work with SQL Server Authentication. But, quoting directly from BOL:

    Users who execute packages that include the SQL Server destination require the "Create global objects" permission. You can grant this permission to users by using the Local Security Policy tool opened from the Administrative Tools menu. If you receive an error message when executing a package that uses the SQL Server destination, make sure that the account running the package has the "Create global objects" permission.

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

    So you probably have a permission issue.

    I'd like to add that an OLE DB Destination is usually a better option. The performance differences are really small when you use the fast load option and it has less "requirements".

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • March 28, 2011 at 7:36 pm

    #1304646

    @crazy4sql

    I was referring to the SQL Server Authentication of the OLEDB Connection Manager. Using SA authentication causes the SQL Server Agent Job to fail.

    @Koen Verbeeck

    That's what I also thought since that permission applies to the user account that executes the job.

    You're probably right, maybe the SA login that I'm using has no permission to run the job or perform BULK INSERT or something.

    I've switched to OLE DB Destination(Fast Load). Just like "Koen Verbeeck " said, the performance difference between the two is negligible And it offers more flexibility since it will not force you to edit the package in the production server and you can test the package during development.

    But I really want to know the cause of the error. I will do some more testing and investigations.

    Thanks guys.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply