Friday, March 23, 2012

Package Permissions in Sql Server

Hi, first post here. Sorry, I know there are a million threads on this kind of topic, but none of them have helped me (nor anything else on the Internet).

Here's my situation (trying to give as much detail as possible):

I have a package that I made on one computer. The package opens an ODBC connection that requires a password, runs a query, does a few quick transformations, and then finishes with a script task destination.

First off, the package runs fine in BIDS. Then, I recently moved all my development stuff to a new server. Package still runs from from within BIDS.

I'm logged on as Administrator, which is the same user SQL Server, and all its parts are running as (including the Agent, and SSIS).

Now, I want to schedule the package to run in a job (which I already have set up with other steps by the way) in Sql Server. I have tried all of the following, and none work:

1) Set the package to encrypt sensitive with password. Then loaded the package into the job step from the file system, went to the command line tab and was prompted for, and entered the password. Didn't work.

2) Set the package to don't save sensitive, and created a configuration file with the password (also tried it with the entire connection string) saved. Created a job step for the package and added the configuration file. Didn't work.

3) With the package changed back to encrypt sensitive with password (and the config file removed), I imported the package into the SSIS Store from the file system. Then I had it set the imported package's security to encrypt with user key. Right clicked the package in the store, and did "Run Package." It runs perfectly, without prompting me for the password. Then I set up a job step, and point it to the package in the SSIS Store. Doesn't work.

4) Set up the package to create a deployment utility. Deployed the package to the SQL Server, using the option to "rely on server storage for encryption" and entering the password for the package. In SQL Server, I see it (under MSDB), I right click, "Run Package", and it runs perfectly without asking for the password. I set up a job step and point it to that package. Doesn't work.

Currently, I'm running the package by creating a simple batch file which calls dtexec for the package (with /DECRYPT mypasswordhere). Then I have this batch file being run under the windows scheduler. This works fine.

So then I tried using this command in a job step with cmdexec, instead of an SSIS step. This fails as well.

The only thing I have not tried yet is setting up a proxy account, but I don't really want to do that, and I don't see how it could help, considering everything is using the same user.

I know I'm not really being specific here with error messages and logs when I say the steps fail, but that's mostly because I'm not sure how to get good error reporting out of this. Any help with that would also be appreciated.

Please help, this is driving me up a wall.

Anyone?

Can anyone maybe help me pinpoint the error here?
|||

Ryan,

Its difficult because everything you say you have done seems to be correct. I always set ProtectionLevel=DontSaveSensitive and use a config file but I see that you have attempted that. If you are doing that then the package should run fine. I am left wondering what error messages you are getting under this scenario.

-Jamie

|||Jamie, thanks for the response, it's good to know someone's at least reading.

About the errors, that's what I meant in my last post, although I guess I worded it poorly. I'm still pretty new to the whole SSIS thing, so I'm not really sure where to get that kind of information. What I was doing is going into the job, and clicking "job history" to bring up the logger(?) and then look at at those errors. Is that right? Are there other places to see the errors?

The thing that really bothers me about this is that (in multiple ways), I'm able to import the package into the package store, and simply right click -> run package, and it works perfectly every time. But then as soon as I point a job step to that same exact package, it fails. How could this be?

When I right click the package and run it, it's running under SSIS, correct? But when it's run from the job step, it's running under the SQL Agent, correct? This seems to be the only difference and possible cause of the problem. When I check the SQL Server configuration manager, I can see that both of those (and everything else for SQL Server) are all running under the same user (Administrator). So what else could be the problem here?
|||

Ryan Hunter wrote:

Jamie, thanks for the response, it's good to know someone's at least reading.

About the errors, that's what I meant in my last post, although I guess I worded it poorly. I'm still pretty new to the whole SSIS thing, so I'm not really sure where to get that kind of information. What I was doing is going into the job, and clicking "job history" to bring up the logger(?) and then look at at those errors. Is that right? Are there other places to see the errors?

The agent job has an output - and that's what you are looking at with 'job history'.

The packages themselves have output logs. Within BIDS when you are designing the package go into the SSIS menu and select 'Logging...'. You get ALOT more useful information from this second method.

Ryan Hunter wrote:

The thing that really bothers me about this is that (in multiple ways), I'm able to import the package into the package store, and simply right click -> run package, and it works perfectly every time. But then as soon as I point a job step to that same exact package, it fails. How could this be?

The most obvious thing to look for is that you are running it as a different user. But according to what you said earlier that is not the case so I'm a bit baffled.

Ryan Hunter wrote:

When I right click the package and run it, it's running under SSIS, correct?

I confess I'm not sure what it runs as under this scenario and I really really should. I'll go check it out now.

But...saying it is "running under SSIS" doesn't make much sense to me. Not sure what that means.

Ryan Hunter wrote:

But when it's run from the job step, it's running under the SQL Agent, correct?

Yes, absolutely correct - it is running as the user that is running the SQL Agent service (unless a proxy is being used).

Ryan Hunter wrote:

This seems to be the only difference and possible cause of the problem. When I check the SQL Server configuration manager, I can see that both of those (and everything else for SQL Server) are all running under the same user (Administrator). So what else could be the problem here?

Don't know to be honest. It does sound to as though its a pemissions issue. If I were you I would implement better logging in order that you can diagnose.

-Jamie

|||

Jamie Thomson wrote:

Ryan Hunter wrote:

When I right click the package and run it, it's running under SSIS, correct?

I confess I'm not sure what it runs as under this scenario and I really really should. I'll go check it out now.

But...saying it is "running under SSIS" doesn't make much sense to me. Not sure what that means.

As far as I can determine when you execute a package from SSMS it still executes as you, the person that clicks the 'Execute' button. Test it out. Create a new package. Drag on a new script task. Copy in the following code:

Imports System

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim vars As Variables

Dts.VariableDispenser.LockForRead("System::UserName")

MsgBox(Dts.Variables(0).Value.ToString)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

and then run it.

-Jamie

|||

Jamie Thomson wrote:

I confess I'm not sure what it runs as under this scenario and I really really should. I'll go check it out now.

But...saying it is "running under SSIS" doesn't make much sense to me. Not sure what that means.


I meant, using this method (run package from the store), is it the "SSIS Service" that I see running in my SQL Server configuration manager that runs the package?

Edit: I see your above post. I still don't see the problem...every way the package could be running is on the same user. I'm logged in as Administrator, and every SQL Server service is running as Administrator.

Jamie Thomson wrote:

Don't know to be honest. It does sound to as though its a pemissions issue. If I were you I would implement better logging in order that you can diagnose.

-Jamie

Ok, I went ahead and read about how to implement the logging. I set it up to log to a txt file for onError and onWarning handlers. Here's the error:

[quote="Error Log"]
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

"Driver's SQLSetConnectAttr failed." Could that be because it's not getting the password for the ODBC Connection?

|||

OK, that's alot more useful.

Do you have a different version of your ODBC driver installed in different places?

-Jamie

|||I'm not sure I completely understand your question, because I'm not sure where exactly the driver would be installed.

However, these servers are brand new, and were just all set up a couple of weeks ago, so anywhere the driver lives, it should be the same version.
|||32 or 64 bit?|||To be honest, I don't even know. How would I check?
|||

Ryan Hunter wrote:

I'm not sure I completely understand your question, because I'm not sure where exactly the driver would be installed.

However, these servers are brand new, and were just all set up a couple of weeks ago, so anywhere the driver lives, it should be the same version.

OK, but you can see yourself from the error message that the problem is something with the ODBC Driver. There's not much I personally can help with there. ODBC connectivity isn't my strong point.

-Jamie

|||My apologies. I was looking back over the error log, and realized I missed the first line when I copy/pasted. Here it is again.

ERROR [S1000] [TimberlineODBC][TimberlineODBCEngine ODBC Driver][DRM File Library]Drive or directory unavailable [IO-WIN 3]
Screen\OD.scr
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

I tried recreating this error by removing the password for the connection in BIDS, and then running it, but I got a different error (invalid password). So it seems that the package is likely getting the password correctly, but failing because that file (od.scr) is unavailable.
|||

Ryan Hunter wrote:

My apologies. I was looking back over the error log, and realized I missed the first line when I copy/pasted. Here it is again.

ERROR [S1000] [TimberlineODBC][TimberlineODBCEngine ODBC Driver][DRM File Library]Drive or directory unavailable [IO-WIN 3]
Screen\OD.scr
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

I tried recreating this error by removing the password for the connection in BIDS, and then running it, but I got a different error (invalid password). So it seems that the package is likely getting the password correctly, but failing because that file (od.scr) is unavailable.

This is a problem with your ODBC Driver, not permissions (I don't think). The problem is right there: "The driver doesn't support the version of ODBC behavior that the application requested "

-Jamie

|||

Ryan Hunter wrote:

To be honest, I don't even know. How would I check?

The quickest might be to ask those who set up the servers.

No comments:

Post a Comment