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.

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.

Owner granting permissions on his objetcs

hi all,
I've run the follwing code:
use adventureworks
go
create login a with password = '123'
create login b with password = '123'
create user a
create user b
go
grant create table on database::adventureworks to a
grant alter schema::dbo to a
grant select on schema::dbo to a
execute as login = 'a'
create table dbo.aTab (col1 int)
grant select on object::dbo.aTab to b
the grant statment failed.
how come an object owner can't grant select permission on his object to
other db users?
what does it take, at the minimum, to allow him to do that?
thanks
tomTom
add
grant control on schema::dbo to a
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:1C3EB310-7915-467C-852C-EF6CFC65CB57@.microsoft.com...
> hi all,
> I've run the follwing code:
> use adventureworks
> go
> create login a with password = '123'
> create login b with password = '123'
> create user a
> create user b
> go
> grant create table on database::adventureworks to a
> grant alter schema::dbo to a
> grant select on schema::dbo to a
> execute as login = 'a'
> create table dbo.aTab (col1 int)
> grant select on object::dbo.aTab to b
> the grant statment failed.
> how come an object owner can't grant select permission on his object to
> other db users?
> what does it take, at the minimum, to allow him to do that?
> thanks
> tom
>
>|||hi Uri,
grant control makes the user have full control on the chema.
I only want him to be able to grant other users select permission on his
table.
thanks,
tom.
"Uri Dimant" wrote:

> Tom
> add
> grant control on schema::dbo to a
>
>
>
> "Tom" <Tom@.discussions.microsoft.com> wrote in message
> news:1C3EB310-7915-467C-852C-EF6CFC65CB57@.microsoft.com...
>
>

Owner and permissions

One thing that seems goofy to me is why an owner of a database has to have
explicit permissions set. I guess I don't understand the mindset of the
whole security getup in 2005. If I'm the owner of a car, yeah, well, I
pretty much have unlimited permissions on that car. Why is this different in
SQL Server?Earl
If you are an owner of the database , all objects (with your default
schema) belong to you. More over if you are a member of sysadmin server
role you have FULL permission on the server level. Can you provide an
example you are worried about?
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:%23CWWTCmyGHA.1304@.TK2MSFTNGP05.phx.gbl...
> One thing that seems goofy to me is why an owner of a database has to have
> explicit permissions set. I guess I don't understand the mindset of the
> whole security getup in 2005. If I'm the owner of a car, yeah, well, I
> pretty much have unlimited permissions on that car. Why is this different
> in SQL Server?
>|||Ahh, perfect example. Take an SQL2000 database and attach to SQL2005. Try to
open a diagram. You get the infamous message about having to be "a valid
owner" then install the diagram support objects. Well, even with the ONLY
system login set as database owner, I continued to get this message (note
that I also changed the compatibility level to '90' as directed in the
Readme). But I had to change the owner to 'sa' in order to install the
database diagram objects.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23%23PYrRmyGHA.1936@.TK2MSFTNGP06.phx.gbl...
> Earl
> If you are an owner of the database , all objects (with your default
> schema) belong to you. More over if you are a member of sysadmin server
> role you have FULL permission on the server level. Can you provide an
> example you are worried about?
>
>
> "Earl" <brikshoe@.newsgroups.nospam> wrote in message
> news:%23CWWTCmyGHA.1304@.TK2MSFTNGP05.phx.gbl...
>> One thing that seems goofy to me is why an owner of a database has to
>> have explicit permissions set. I guess I don't understand the mindset of
>> the whole security getup in 2005. If I'm the owner of a car, yeah, well,
>> I pretty much have unlimited permissions on that car. Why is this
>> different in SQL Server?
>|||Earl
No, it is not a perfect one , because it is not permissions issue at all.
You can use diagrams on databases that being created in SQL Server 2005
only
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:OaNyY8oyGHA.476@.TK2MSFTNGP06.phx.gbl...
> Ahh, perfect example. Take an SQL2000 database and attach to SQL2005. Try
> to open a diagram. You get the infamous message about having to be "a
> valid owner" then install the diagram support objects. Well, even with the
> ONLY system login set as database owner, I continued to get this message
> (note that I also changed the compatibility level to '90' as directed in
> the Readme). But I had to change the owner to 'sa' in order to install the
> database diagram objects.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23%23PYrRmyGHA.1936@.TK2MSFTNGP06.phx.gbl...
>> Earl
>> If you are an owner of the database , all objects (with your default
>> schema) belong to you. More over if you are a member of sysadmin server
>> role you have FULL permission on the server level. Can you provide an
>> example you are worried about?
>>
>>
>> "Earl" <brikshoe@.newsgroups.nospam> wrote in message
>> news:%23CWWTCmyGHA.1304@.TK2MSFTNGP05.phx.gbl...
>> One thing that seems goofy to me is why an owner of a database has to
>> have explicit permissions set. I guess I don't understand the mindset of
>> the whole security getup in 2005. If I'm the owner of a car, yeah, well,
>> I pretty much have unlimited permissions on that car. Why is this
>> different in SQL Server?
>>
>

Owner and permissions

One thing that seems goofy to me is why an owner of a database has to have
explicit permissions set. I guess I don't understand the mindset of the
whole security getup in 2005. If I'm the owner of a car, yeah, well, I
pretty much have unlimited permissions on that car. Why is this different in
SQL Server?Earl
If you are an owner of the database , all objects (with your default
schema) belong to you. More over if you are a member of sysadmin server
role you have FULL permission on the server level. Can you provide an
example you are worried about?
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:%23CWWTCmyGHA.1304@.TK2MSFTNGP05.phx.gbl...
> One thing that seems goofy to me is why an owner of a database has to have
> explicit permissions set. I guess I don't understand the mindset of the
> whole security getup in 2005. If I'm the owner of a car, yeah, well, I
> pretty much have unlimited permissions on that car. Why is this different
> in SQL Server?
>|||Ahh, perfect example. Take an SQL2000 database and attach to SQL2005. Try to
open a diagram. You get the infamous message about having to be "a valid
owner" then install the diagram support objects. Well, even with the ONLY
system login set as database owner, I continued to get this message (note
that I also changed the compatibility level to '90' as directed in the
Readme). But I had to change the owner to 'sa' in order to install the
database diagram objects.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23%23PYrRmyGHA.1936@.TK2MSFTNGP06.phx.gbl...
> Earl
> If you are an owner of the database , all objects (with your default
> schema) belong to you. More over if you are a member of sysadmin server
> role you have FULL permission on the server level. Can you provide an
> example you are worried about?
>
>
> "Earl" <brikshoe@.newsgroups.nospam> wrote in message
> news:%23CWWTCmyGHA.1304@.TK2MSFTNGP05.phx.gbl...
>|||Earl
No, it is not a perfect one , because it is not permissions issue at all.
You can use diagrams on databases that being created in SQL Server 2005
only
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:OaNyY8oyGHA.476@.TK2MSFTNGP06.phx.gbl...
> Ahh, perfect example. Take an SQL2000 database and attach to SQL2005. Try
> to open a diagram. You get the infamous message about having to be "a
> valid owner" then install the diagram support objects. Well, even with the
> ONLY system login set as database owner, I continued to get this message
> (note that I also changed the compatibility level to '90' as directed in
> the Readme). But I had to change the owner to 'sa' in order to install the
> database diagram objects.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23%23PYrRmyGHA.1936@.TK2MSFTNGP06.phx.gbl...
>

Overlapping Permissions

I would think the following scenario should work, but it does not:
I have a table, Products, for which all users, via an NT domain group (e.g.
Domain Users) have only select permissions.
There is another group, ProductManagers, who are also members of the above
group, who need update, delete, and insert permissions. To accomplish this,
I
created a database role ProductMgmt, and added the ProductManagers to it.
This role has select, insert, update and delete permissions on the table.
The members of this group, however, get an error when attempting to delete
from the table. These members belong to both the Domain User and
ProductManagers groups.
I've also given the ProductManager group full permissions on the table. I'm
confused as to why all of this is not working, obviously I'm missing
something.
Thanks for any assistance,
TomtDoes the NT group which has only select permissions have a
deny on delete? Do any users or groups have deny set on the
table?
Permissions are cumulative but deny will take precedence.
-Sue
On Tue, 9 Nov 2004 14:51:03 -0800, "TomT" <tomt@.tomt.com>
wrote:

>I would think the following scenario should work, but it does not:
>I have a table, Products, for which all users, via an NT domain group (e.g.
>Domain Users) have only select permissions.
>There is another group, ProductManagers, who are also members of the above
>group, who need update, delete, and insert permissions. To accomplish this,
I
>created a database role ProductMgmt, and added the ProductManagers to it.
>This role has select, insert, update and delete permissions on the table.
>The members of this group, however, get an error when attempting to delete
>from the table. These members belong to both the Domain User and
>ProductManagers groups.
>I've also given the ProductManager group full permissions on the table. I'm
>confused as to why all of this is not working, obviously I'm missing
>something.
>Thanks for any assistance,
>Tomt
>|||Sue,
Thanks for your reply. No, there are no deny's on delete. I did know that
one, but am missing something...
Thanks
Tom
"Sue Hoegemeier" wrote:

> Does the NT group which has only select permissions have a
> deny on delete? Do any users or groups have deny set on the
> table?
> Permissions are cumulative but deny will take precedence.
> -Sue
> On Tue, 9 Nov 2004 14:51:03 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||And there are no other Windows groups and no other roles in
that database? Just the two roles and the two NT groups?
And members of the ProductMgmt role can select, insert and
update but not delete?
-Sue
On Tue, 9 Nov 2004 15:44:04 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>Sue,
>Thanks for your reply. No, there are no deny's on delete. I did know that
>one, but am missing something...
>Thanks
>Tom
>"Sue Hoegemeier" wrote:
>|||There's the Domain Users and two other non-NT, SQL Server accounts for web
access to the table.
There are no other roles other than the built-in roles. That group has
select, insert, update and delete permissions.
I'm going to have them try it again tomorrow, I might have overlooked
checking the delete permission, which is just due to trying to do too many
things at once...
I'll post the results tomorrow. Thanks for your assistance with this.
Tom
"Sue Hoegemeier" wrote:

> And there are no other Windows groups and no other roles in
> that database? Just the two roles and the two NT groups?
> And members of the ProductMgmt role can select, insert and
> update but not delete?
> -Sue
> On Tue, 9 Nov 2004 15:44:04 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||Tom
Grant them EXECUTE permission on SP that perform DELETE/INSERT/UPDATE on
this table.
"TomT" <tomt@.tomt.com> wrote in message
news:F6DB5A9A-6ADF-4F89-89E8-9656A8962BFF@.microsoft.com...[vbcol=seagreen]
> There's the Domain Users and two other non-NT, SQL Server accounts for web
> access to the table.
> There are no other roles other than the built-in roles. That group has
> select, insert, update and delete permissions.
> I'm going to have them try it again tomorrow, I might have overlooked
> checking the delete permission, which is just due to trying to do too many
> things at once...
> I'll post the results tomorrow. Thanks for your assistance with this.
> Tom
> "Sue Hoegemeier" wrote:
>
that[vbcol=seagreen]
group (e.g.[vbcol=seagreen]
above[vbcol=seagreen]
accomplish this, I[vbcol=seagreen]
to it.[vbcol=seagreen]
table.[vbcol=seagreen]
delete[vbcol=seagreen]
table. I'm[vbcol=seagreen]|||You really should check the other permissions as well as it
could make it easier for you to determine what has been
missed. Check the select, insert and update permissions as
well.
-Sue
On Tue, 9 Nov 2004 21:31:03 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>There's the Domain Users and two other non-NT, SQL Server accounts for web
>access to the table.
>There are no other roles other than the built-in roles. That group has
>select, insert, update and delete permissions.
>I'm going to have them try it again tomorrow, I might have overlooked
>checking the delete permission, which is just due to trying to do too many
>things at once...
>I'll post the results tomorrow. Thanks for your assistance with this.
>Tom
>"Sue Hoegemeier" wrote:
>|||I checked them all, for that particular group, and still no go. I have to
grant the permissions for the Domain Users group for insert, delete, etc.
otherwise the group I really need to have this access does not.
To summarize: Two groups (NT) Domain Users, to which all users belong,
member of the public role, and ProductManagers, member of public and
ProductMgmt roles.
A user, Rod, belongs to both Domain Users and ProductManagers groups.
ProductMangers have select, insert, delete and update permissions on table;
Domain Users have Select permission only, no other permissions granted or
denied.
Database role ProductMgmt has full permissions on the table.
With the scenario above, Rod cannot delete from the table. I have to grant
delete permissions to Domain Users in order for him to be able to delete row
s
from the table.
I gather from your replies that this should work, and I have set it up
correctly, is that right?
Thanks,
Tom
"Sue Hoegemeier" wrote:

> You really should check the other permissions as well as it
> could make it easier for you to determine what has been
> missed. Check the select, insert and update permissions as
> well.
> -Sue
> On Tue, 9 Nov 2004 21:31:03 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||Yes it will work so you are still missing something. I can't
reproduce the issue rebuilding with the same groups and
roles - it works fine on my end.
Try using xp_logininfo to determine the group membership and
dsiplay information on the Product Managers group at the
Windows level.
-Sue
On Wed, 10 Nov 2004 08:45:01 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>I checked them all, for that particular group, and still no go. I have to
>grant the permissions for the Domain Users group for insert, delete, etc.
>otherwise the group I really need to have this access does not.
>To summarize: Two groups (NT) Domain Users, to which all users belong,
>member of the public role, and ProductManagers, member of public and
>ProductMgmt roles.
>A user, Rod, belongs to both Domain Users and ProductManagers groups.
>ProductMangers have select, insert, delete and update permissions on table;
>Domain Users have Select permission only, no other permissions granted or
>denied.
>Database role ProductMgmt has full permissions on the table.
>With the scenario above, Rod cannot delete from the table. I have to grant
>delete permissions to Domain Users in order for him to be able to delete ro
ws
>from the table.
>I gather from your replies that this should work, and I have set it up
>correctly, is that right?
>Thanks,
>Tom
>"Sue Hoegemeier" wrote:
>|||Sue,
I found the problem, the person who set up the NT user group
ProductManagers, set it up as a distribution group, not a security group.
Once that was fixed, everything works correctly.
BTW, I assume this would still work without the database role, i.e., just
the ProductManager group having the delete, etc. permissions assigned,
correct?
thanks for your help and patience,
Tom
"Sue Hoegemeier" wrote:

> Yes it will work so you are still missing something. I can't
> reproduce the issue rebuilding with the same groups and
> roles - it works fine on my end.
> Try using xp_logininfo to determine the group membership and
> dsiplay information on the Product Managers group at the
> Windows level.
> -Sue
> On Wed, 10 Nov 2004 08:45:01 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>