FDA Compliance and SQL 2000

We have an application using SQL 2000 database that must be FDA compliant. Anyone with FDA compliance experience? Know any products that will audit database changes? How about validation of backups and restores.

Thanks!

I beleive that Quest Software has products to audit schema changes to the database.

SQL Server can validate backups nativly. Quest may have a third party production which can do this as well.

|||For auditing, Idera has a really good product called compliance manager that will enable you to track pretty much whatever you need to track. If you are only tracking data changes, you can always write you own custom data auditing solution without too much grief. Hope this helps.
Tim|||

Thanks for all your help. This gets me going in the right direction.

GDM

Failure to run when using DTC

Does anyone hany any experience of using SSIS with MS DTC?
I have a package that runs successfully. When I switch it to using transactions (i.e. TransactionOption=Required) it fails. I get the following messages in the log:
-Starting distributed transaction for this container.
-Failed to acquire connection "<connection-manager-name>". Connection may not be configured correctly or you may not have the right permissions on this connection.
-Aborting the current distributed transaction.

So - it seems it is having trouble enlisting that connection manager in a distributed transaction. My connection manager points at a SQL Server database on a different server to where I am running the package.
The connection string on the connection manager is: Data Source=ABENTAH3D02\DEVDBINSTANCE;Initial Catalog=CUESeerMetadata;Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;

OK, so I kind of know where the error is but I don't know exactly what's causing it or how to go about diagnosing the problem. I don't have much experience of MS DTC although I have used it from SSIS in the past successfully.

Does anyone know the pre-requisites for using MS DTC?
How can I go about finding the problem?

Thanks
JamieThis sounds like a DTC issue with your permissions.
I'm not sure what the problem is, but here are a few things to check:
Are you able to enlist in a transaction on the remote machine where the SServer is? Run the package on that machine if you can. Is it successful there?
Is DTC started and running on both machines?
Do you have the priviledges to access the connection on the remote machine?
You might check the Windows Event Viewer on both machines to see if there are any messages that may give you more information about the problem.
HTH,
K|||I don't have the ability to run the package on the SQL box - or more accurately don't have time to install all the pre-reqs- but I'm pretyt sure that it IS a problem enlisting remote connections in the txn. i.e. I have a local connection in my package which enlists in the transaction without a problem.

In my local dtc trace log I have the following messages (many times)
"transaction got begun, description : '<NULL>"
"received request to abort the transaction from beginner"
"transaction is aborting"
"transaction has been aborted"

Nothing in the event viewer.

I'm working on it being a priviledges problem! Unfortunately I have no experience with DTC so its a slow process.

-Jamie|||Jamie, is DTC running on the server machine, i.e. ABENTAH3D02?

This can be found out through the following steps:
1. Open "Control Panel"
2. Open "Administrative Tools"
3. Open "Component Services"
4. Expand "Computer", right-click on "My Computer", switch to page "MSDTC"

Look for Service Control Status. Is it "Started"?|||

Runying Mao wrote:

Jamie, is DTC running on the server machine, i.e. ABENTAH3D02?

This can be found out through the following steps:
1. Open "Control Panel"
2. Open "Administrative Tools"
3. Open "Component Services"
4. Expand "Computer", right-click on "My Computer", switch to page "MSDTC"

Look for Service Control Status. Is it "Started"?

Yeah it is. First thing I checked |||

Ok. Good.

Then in the same page "MSDTC", click "Security Configuration". There are "Allow Inbound" and "Allow Outbound" in Transaction Manager Communication. Are they both checked?

|||

Runying Mao wrote:

Ok. Good.

Then in the same page "MSDTC", click "Security Configuration". There are "Allow Inbound" and "Allow Outbound" in Transaction Manager Communication. Are they both checked?

Yes they are!

Can the choice of logon account affect it? Currently it is set to "NT Authority\NetworkService" on both machines.

I've also tried selecting "No authentication required" on the server. It didn't make any difference.

In fact, here are all the settings on the server (as taken from the event log):

MS DTC started with the following settings (OFF = 0 and ON = 1):
Security Configuration:
Network Administration of Transactions = 1,
Network Clients = 1,
Inbound Distributed Transactions using Native MSDTC Protocol = 1,
Outbound Distributed Transactions using Native MSDTC Protocol = 1,
Transaction Internet Protocol (TIP) = 0,
XA Transactions = 0
Filtering Duplicate events = 1
And here are the local settings:

MS DTC started with the following settings:
Security Configuration (OFF = 0 and ON = 1):
Network Administration of Transactions = 1,
Network Clients = 1,
Inbound Distributed Transactions using Native MSDTC Protocol = 1,
Outbound Distributed Transactions using Native MSDTC Protocol = 1,
Transaction Internet Protocol (TIP) = 0,
XA Transactions = 0

-Jamie|||IGNORE THIS!!!!

I've got it working. You've got to turn Windows Firewall off

http://support.microsoft.com/default.aspx/kb/839279
YEEESSSSS!!!!

(Very happy right now )

-Jamie|||

This DTC seems not to work...

I have a package with a single data flow that copies a table from Informix to SQL Server. It finishes "all green", but ask me if I got any data on the target table...

Failure to run when using DTC

Does anyone hany any experience of using SSIS with MS DTC?
I have a package that runs successfully. When I switch it to using transactions (i.e. TransactionOption=Required) it fails. I get the following messages in the log:
-Starting distributed transaction for this container.
-Failed to acquire connection "<connection-manager-name>". Connection may not be configured correctly or you may not have the right permissions on this connection.
-Aborting the current distributed transaction.

So - it seems it is having trouble enlisting that connection manager in a distributed transaction. My connection manager points at a SQL Server database on a different server to where I am running the package.
The connection string on the connection manager is: Data Source=ABENTAH3D02\DEVDBINSTANCE;Initial Catalog=CUESeerMetadata;Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;

OK, so I kind of know where the error is but I don't know exactly what's causing it or how to go about diagnosing the problem. I don't have much experience of MS DTC although I have used it from SSIS in the past successfully.

Does anyone know the pre-requisites for using MS DTC?
How can I go about finding the problem?

Thanks
JamieThis sounds like a DTC issue with your permissions.
I'm not sure what the problem is, but here are a few things to check:
Are you able to enlist in a transaction on the remote machine where the SServer is? Run the package on that machine if you can. Is it successful there?
Is DTC started and running on both machines?
Do you have the priviledges to access the connection on the remote machine?
You might check the Windows Event Viewer on both machines to see if there are any messages that may give you more information about the problem.
HTH,
K|||I don't have the ability to run the package on the SQL box - or more accurately don't have time to install all the pre-reqs- but I'm pretyt sure that it IS a problem enlisting remote connections in the txn. i.e. I have a local connection in my package which enlists in the transaction without a problem.

In my local dtc trace log I have the following messages (many times)
"transaction got begun, description : '<NULL>"
"received request to abort the transaction from beginner"
"transaction is aborting"
"transaction has been aborted"

Nothing in the event viewer.

I'm working on it being a priviledges problem! Unfortunately I have no experience with DTC so its a slow process.

-Jamie|||Jamie, is DTC running on the server machine, i.e. ABENTAH3D02?

This can be found out through the following steps:
1. Open "Control Panel"
2. Open "Administrative Tools"
3. Open "Component Services"
4. Expand "Computer", right-click on "My Computer", switch to page "MSDTC"

Look for Service Control Status. Is it "Started"?|||

Runying Mao wrote:

Jamie, is DTC running on the server machine, i.e. ABENTAH3D02?

This can be found out through the following steps:
1. Open "Control Panel"
2. Open "Administrative Tools"
3. Open "Component Services"
4. Expand "Computer", right-click on "My Computer", switch to page "MSDTC"

Look for Service Control Status. Is it "Started"?

Yeah it is. First thing I checked |||

Ok. Good.

Then in the same page "MSDTC", click "Security Configuration". There are "Allow Inbound" and "Allow Outbound" in Transaction Manager Communication. Are they both checked?

|||

Runying Mao wrote:

Ok. Good.

Then in the same page "MSDTC", click "Security Configuration". There are "Allow Inbound" and "Allow Outbound" in Transaction Manager Communication. Are they both checked?

Yes they are!

Can the choice of logon account affect it? Currently it is set to "NT Authority\NetworkService" on both machines.

I've also tried selecting "No authentication required" on the server. It didn't make any difference.

In fact, here are all the settings on the server (as taken from the event log):

MS DTC started with the following settings (OFF = 0 and ON = 1):
Security Configuration:
Network Administration of Transactions = 1,
Network Clients = 1,
Inbound Distributed Transactions using Native MSDTC Protocol = 1,
Outbound Distributed Transactions using Native MSDTC Protocol = 1,
Transaction Internet Protocol (TIP) = 0,
XA Transactions = 0
Filtering Duplicate events = 1
And here are the local settings:

MS DTC started with the following settings:
Security Configuration (OFF = 0 and ON = 1):
Network Administration of Transactions = 1,
Network Clients = 1,
Inbound Distributed Transactions using Native MSDTC Protocol = 1,
Outbound Distributed Transactions using Native MSDTC Protocol = 1,
Transaction Internet Protocol (TIP) = 0,
XA Transactions = 0

-Jamie|||IGNORE THIS!!!!

I've got it working. You've got to turn Windows Firewall off

http://support.microsoft.com/default.aspx/kb/839279
YEEESSSSS!!!!

(Very happy right now )

-Jamie|||

This DTC seems not to work...

I have a package with a single data flow that copies a table from Informix to SQL Server. It finishes "all green", but ask me if I got any data on the target table...