row level security and Integration services bulk inserts don't work

Hi i followed Microsofts "Implementing Row-and-Cell-Level Security in Classified Databases Using SQL Server 2005"

this works fine when i insert delete data on a normal script (mangement studio)

my project runs in a SSIS package, different users. i cannot do a bulk insert using OLEDB data Destination i get the following error

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Conflicting locking hints are specified for table "dbo.tblUniqueLabelMarking". This may be caused by a conflicting hint specified for a view.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Conflicting locking hints are specified for table "dbo.tblUniqueLabelMarking". This may be caused by a conflicting hint specified for a view.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Conflicting locking hints are specified for table "dbo.tblUniqueLabel". This may be caused by a conflicting hint specified for a view.".
Error: 0xC0209029 at Data Flow Task, OLE DB Destination 1 [1741]: The "input "OLE DB Destination Input" (1754)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (1754)" specifies failure on error. An error occurred on the specified object of the specified component.

Are you using the "fast load" data access mode with the "table lock" option checked?|||

Hi Duane.

Access Mode = OpenRowset Using FastLoad

Fast Load Options = TABLOCK,CHECK_CONSTRAINTS

these are the default values in the OLE DB Destination. is this correct?

|||

ajedi2k wrote:

Hi Duane.

Access Mode = OpenRowset Using FastLoad

Fast Load Options = TABLOCK,CHECK_CONSTRAINTS

these are the default values in the OLE DB Destination. is this correct?

Yes, those are the default values.

What type of connection manager are you using in the OLE DB destination?

|||

I'm using a OLE DB connection to an SQL server 2005 DB.

is this what you mean?

|||

my bad,

i was actually inserting to a view, and view had triggers.

must set FastLoadOptions = FIRE_TRIGGERS

works fine

row level security and Integration services bulk inserts don't work

Hi i followed Microsofts "Implementing Row-and-Cell-Level Security in Classified Databases Using SQL Server 2005"

this works fine when i insert delete data on a normal script (mangement studio)

my project runs in a SSIS package, different users. i cannot do a bulk insert using OLEDB data Destination i get the following error

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Conflicting locking hints are specified for table "dbo.tblUniqueLabelMarking". This may be caused by a conflicting hint specified for a view.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Conflicting locking hints are specified for table "dbo.tblUniqueLabelMarking". This may be caused by a conflicting hint specified for a view.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Conflicting locking hints are specified for table "dbo.tblUniqueLabel". This may be caused by a conflicting hint specified for a view.".
Error: 0xC0209029 at Data Flow Task, OLE DB Destination 1 [1741]: The "input "OLE DB Destination Input" (1754)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (1754)" specifies failure on error. An error occurred on the specified object of the specified component.

Are you using the "fast load" data access mode with the "table lock" option checked?|||

Hi Duane.

Access Mode = OpenRowset Using FastLoad

Fast Load Options = TABLOCK,CHECK_CONSTRAINTS

these are the default values in the OLE DB Destination. is this correct?

|||

ajedi2k wrote:

Hi Duane.

Access Mode = OpenRowset Using FastLoad

Fast Load Options = TABLOCK,CHECK_CONSTRAINTS

these are the default values in the OLE DB Destination. is this correct?

Yes, those are the default values.

What type of connection manager are you using in the OLE DB destination?

|||

I'm using a OLE DB connection to an SQL server 2005 DB.

is this what you mean?

|||

my bad,

i was actually inserting to a view, and view had triggers.

must set FastLoadOptions = FIRE_TRIGGERS

works fine

Row Level Security

I am looking into row level security and found a good article:
Title: Implementing Row- and Cell-Level Security in Classified
Databases Using SQL Server 2005
URL: http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx
Is this still a good approach to take for a secure database? If not,
what would you recommend?
Are you aware of any sample project and DB that implements the above
approach?
ThanksHi,
as there is no row-level security bya default in SQL Server you will always
have to implement it for yourself. Implementation which I have seen
implemeted row level security (using the SUser_Name) with functions like
suser_Name() up to solution with passing hashes or certificates to SQL
Server. Row Level security is still an appropiate solution for making a
granular access to your data.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--
<hufaunder@.yahoo.com> wrote in message
news:1173046321.597432.77820@.n33g2000cwc.googlegroups.com...
>I am looking into row level security and found a good article:
> Title: Implementing Row- and Cell-Level Security in Classified
> Databases Using SQL Server 2005
> URL: http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx
> Is this still a good approach to take for a secure database? If not,
> what would you recommend?
> Are you aware of any sample project and DB that implements the above
> approach?
> Thanks
>