FlatFile connection and security

My DTS package, deployed and run from the file system, works just fine for me, but fails when someone else runs it. The only explicit error from the dtexec command is:

Error: 2005-06-24 12:35:03.33
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error

This error just started when we tried to point to a file on a network server instead of a file local to the machine running the package. The other person definitely has access rights to that network server, but the package fails anyway.

One possible solution might be to specify a username/password for the file, but I don't see how to include that in the connection string. Alternatively, this may be a more general security issue with the whole package. I first turned to BOL for some explanations and answers, but wasn't able to find anything specific to my problem.

Any help or suggestions will be greatly appreciated. Thanks!
Phil
This is because the default protection level is to encrypt with a user key. That means only a user on his/her machine can open the package.

You probably want to use Package Password here instead.|||

I am getting a similar error on a DTS package created by me and migrated by me and executed by me via Dtexec. Shouldn't ssql 2000 ownership carry over to 2005? The package is not a flat file. And, by the way, where does the "DTS Property" come in?

thanks

Ted

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 9:24:56 AM
Error: 2006-02-08 09:24:56.50
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error
Error: 2006-02-08 09:24:56.51
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error

|||

SSIS is a completely new product so I don't think it follows that DTS behaviour should make it into SSIS. Furthermore, there's been a very high profile tightening up of security within Microsoft products and the behaviour you're seeing here is probably as a result of that.

The reason you see references to "DTS" in various places is for legacy reasons. Alot of the product had already been built before the name change came about.

-Jamie

sql

FlatFile connection and security

My DTS package, deployed and run from the file system, works just fine for me, but fails when someone else runs it. The only explicit error from the dtexec command is:

Error: 2005-06-24 12:35:03.33
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error

This error just started when we tried to point to a file on a network server instead of a file local to the machine running the package. The other person definitely has access rights to that network server, but the package fails anyway.

One possible solution might be to specify a username/password for the file, but I don't see how to include that in the connection string. Alternatively, this may be a more general security issue with the whole package. I first turned to BOL for some explanations and answers, but wasn't able to find anything specific to my problem.

Any help or suggestions will be greatly appreciated. Thanks!
PhilThis is because the default protection level is to encrypt with a user key. That means only a user on his/her machine can open the package.

You probably want to use Package Password here instead.|||

I am getting a similar error on a DTS package created by me and migrated by me and executed by me via Dtexec. Shouldn't ssql 2000 ownership carry over to 2005? The package is not a flat file. And, by the way, where does the "DTS Property" come in?

thanks

Ted

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 9:24:56 AM
Error: 2006-02-08 09:24:56.50
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error
Error: 2006-02-08 09:24:56.51
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error

|||

SSIS is a completely new product so I don't think it follows that DTS behaviour should make it into SSIS. Furthermore, there's been a very high profile tightening up of security within Microsoft products and the behaviour you're seeing here is probably as a result of that.

The reason you see references to "DTS" in various places is for legacy reasons. Alot of the product had already been built before the name change came about.

-Jamie

Fixing consistency of a MDF file (when detached)

I have a DB that has a torn page error, error 823, and is detached. It
fails when reattaching because of the torn page error. Is there a way to
fix the MDF file when the corresponding DB is not attached to the SQL
server?
Thanks in advance
--
brandyg@.online.microsoft.com [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.AFAIK there is nothing published in public. The best thing you can do is to
contact the PSS.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Brandy Griffin [MSFT]" <brandyg@.online.microsoft.com> wrote in message
news:O4UtMIIWDHA.2256@.TK2MSFTNGP10.phx.gbl...
> I have a DB that has a torn page error, error 823, and is detached. It
> fails when reattaching because of the torn page error. Is there a way to
> fix the MDF file when the corresponding DB is not attached to the SQL
> server?
> Thanks in advance
> --
> brandyg@.online.microsoft.com [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
>
>

First sp after re-start fails because of User-defined type

Hi,
I'm having a problem with SQL Server 7. If I restart the
server, and the first stroed proc I run contains a
reference to a user defined data type then the query fails
because the data type is not recognised. (In this case I'm
creating a temporary table in the stored proc, where one
column in the table uses the user defined data type)
A workaround is to run another stored proc first, and then
everything is fine.
Has anyone got a more elegant solution or reason why this
would happen?
Thanks,
Neil
Seems to be some issue with a user defined datatype not existing in tempdb. Hard to ell without a
repro. Perhaps it is as easy as creating that dt in the model database so you know it will exist in
tempdb?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
> Hi,
> I'm having a problem with SQL Server 7. If I restart the
> server, and the first stroed proc I run contains a
> reference to a user defined data type then the query fails
> because the data type is not recognised. (In this case I'm
> creating a temporary table in the stored proc, where one
> column in the table uses the user defined data type)
> A workaround is to run another stored proc first, and then
> everything is fine.
> Has anyone got a more elegant solution or reason why this
> would happen?
> Thanks,
> Neil
|||I don't think so - In the SP,before I create the temporary
table, I copy all the user data types to the tempdb, but
this isn't being run. In fact, I put some logging calls in
the SP and ran it from Query analyzer, and nothing is run.
It seems that SQL Server is doing some sort of
compilation/validation on the SP and failing it before any
attempt is made to run it. Running some other query seems
to make it 'aware' of the user defined data types, and
then it is happy with the original SP.
Thanks anyway,
Neil

>--Original Message--
>Seems to be some issue with a user defined datatype not
existing in tempdb. Hard to ell without a
>repro. Perhaps it is as easy as creating that dt in the
model database so you know it will exist in
>tempdb?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
fails[vbcol=seagreen]
I'm[vbcol=seagreen]
then[vbcol=seagreen]
this
>
>.
>
|||It seems I spoke too soon! The following code reproduces
the problem:
CREATE PROCEDURE MyUDTTest AS
BEGIN
declare @.stmt as nvarchar(255)
set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
sp_addtype ''udtBuySell'' , ''char(1)'''
exec sp_executesql @.stmt
CREATE TABLE #MyTable (MyColumn udtBuySell)
END
trying to run this SP after a stop/start gives the
following error:
Server: Msg 2715, Level 16, State 7, Procedure MyUDTTest,
Line 10
Column or parameter #1: Cannot find data type udtBuySell.
in tempdb create the udtBuySell data type. Then drop it.
Now the sp will run.
Any thoughts?
Neil

>--Original Message--
>Seems to be some issue with a user defined datatype not
existing in tempdb. Hard to ell without a
>repro. Perhaps it is as easy as creating that dt in the
model database so you know it will exist in
>tempdb?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
fails[vbcol=seagreen]
I'm[vbcol=seagreen]
then[vbcol=seagreen]
this
>
>.
>
|||This is a scoping problem. Since the UDT is created in dynamic SQL, the datatype doesn't exist when
SQL Server parses the code in the procedure. Create the datatype in model instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:244901c4a176$b0fdea60$a601280a@.phx.gbl...[vbcol=seagreen]
> It seems I spoke too soon! The following code reproduces
> the problem:
> CREATE PROCEDURE MyUDTTest AS
> BEGIN
> declare @.stmt as nvarchar(255)
> set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
> sp_addtype ''udtBuySell'' , ''char(1)'''
> exec sp_executesql @.stmt
> CREATE TABLE #MyTable (MyColumn udtBuySell)
> END
> trying to run this SP after a stop/start gives the
> following error:
> Server: Msg 2715, Level 16, State 7, Procedure MyUDTTest,
> Line 10
> Column or parameter #1: Cannot find data type udtBuySell.
> in tempdb create the udtBuySell data type. Then drop it.
> Now the sp will run.
> Any thoughts?
> Neil
>
> existing in tempdb. Hard to ell without a
> model database so you know it will exist in
> message
> fails
> I'm
> then
> this
|||Tibor,
Thanks very much for your help. Creating the data type in
model has been ruled out from above (!) as it would mean
remembering to change it in two places (in the working db
and in model).
I'm curious as to why the procedure should work after the
datatype has been created then dropped in tempdb. I can
see what you are saying about scoping, but why would it be
OK after creating/dropping the type in tempdb? It still
doesn't exist in tempdb after all...
Our current plan is to run a job when SQLAgent starts to
copy the user types from our working db to tempdb (so now
we have to remember to restart the server after a change
to the user types - where will it all end!)
Thanks again for your interest in this problem,
Neil

>--Original Message--
>This is a scoping problem. Since the UDT is created in
dynamic SQL, the datatype doesn't exist when
>SQL Server parses the code in the procedure. Create the
datatype in model instead.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:244901c4a176$b0fdea60$a601280a@.phx.gbl...
MyUDTTest,[vbcol=seagreen]
udtBuySell.[vbcol=seagreen]
the[vbcol=seagreen]
case[vbcol=seagreen]
one
>
>.
>

First sp after re-start fails because of User-defined type

Hi,
I'm having a problem with SQL Server 7. If I restart the
server, and the first stroed proc I run contains a
reference to a user defined data type then the query fails
because the data type is not recognised. (In this case I'm
creating a temporary table in the stored proc, where one
column in the table uses the user defined data type)
A workaround is to run another stored proc first, and then
everything is fine.
Has anyone got a more elegant solution or reason why this
would happen?
Thanks,
NeilSeems to be some issue with a user defined datatype not existing in tempdb. Hard to ell without a
repro. Perhaps it is as easy as creating that dt in the model database so you know it will exist in
tempdb?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
> Hi,
> I'm having a problem with SQL Server 7. If I restart the
> server, and the first stroed proc I run contains a
> reference to a user defined data type then the query fails
> because the data type is not recognised. (In this case I'm
> creating a temporary table in the stored proc, where one
> column in the table uses the user defined data type)
> A workaround is to run another stored proc first, and then
> everything is fine.
> Has anyone got a more elegant solution or reason why this
> would happen?
> Thanks,
> Neil|||I don't think so - In the SP,before I create the temporary
table, I copy all the user data types to the tempdb, but
this isn't being run. In fact, I put some logging calls in
the SP and ran it from Query analyzer, and nothing is run.
It seems that SQL Server is doing some sort of
compilation/validation on the SP and failing it before any
attempt is made to run it. Running some other query seems
to make it 'aware' of the user defined data types, and
then it is happy with the original SP.
Thanks anyway,
Neil
>--Original Message--
>Seems to be some issue with a user defined datatype not
existing in tempdb. Hard to ell without a
>repro. Perhaps it is as easy as creating that dt in the
model database so you know it will exist in
>tempdb?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message
>news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
>> Hi,
>> I'm having a problem with SQL Server 7. If I restart the
>> server, and the first stroed proc I run contains a
>> reference to a user defined data type then the query
fails
>> because the data type is not recognised. (In this case
I'm
>> creating a temporary table in the stored proc, where one
>> column in the table uses the user defined data type)
>> A workaround is to run another stored proc first, and
then
>> everything is fine.
>> Has anyone got a more elegant solution or reason why
this
>> would happen?
>> Thanks,
>> Neil
>
>.
>|||This is a scoping problem. Since the UDT is created in dynamic SQL, the datatype doesn't exist when
SQL Server parses the code in the procedure. Create the datatype in model instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:244901c4a176$b0fdea60$a601280a@.phx.gbl...
> It seems I spoke too soon! The following code reproduces
> the problem:
> CREATE PROCEDURE MyUDTTest AS
> BEGIN
> declare @.stmt as nvarchar(255)
> set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
> sp_addtype ''udtBuySell'' , ''char(1)'''
> exec sp_executesql @.stmt
> CREATE TABLE #MyTable (MyColumn udtBuySell)
> END
> trying to run this SP after a stop/start gives the
> following error:
> Server: Msg 2715, Level 16, State 7, Procedure MyUDTTest,
> Line 10
> Column or parameter #1: Cannot find data type udtBuySell.
> in tempdb create the udtBuySell data type. Then drop it.
> Now the sp will run.
> Any thoughts?
> Neil
>
> >--Original Message--
> >Seems to be some issue with a user defined datatype not
> existing in tempdb. Hard to ell without a
> >repro. Perhaps it is as easy as creating that dt in the
> model database so you know it will exist in
> >tempdb?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Neil" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
> >> Hi,
> >>
> >> I'm having a problem with SQL Server 7. If I restart the
> >> server, and the first stroed proc I run contains a
> >> reference to a user defined data type then the query
> fails
> >> because the data type is not recognised. (In this case
> I'm
> >> creating a temporary table in the stored proc, where one
> >> column in the table uses the user defined data type)
> >>
> >> A workaround is to run another stored proc first, and
> then
> >> everything is fine.
> >>
> >> Has anyone got a more elegant solution or reason why
> this
> >> would happen?
> >>
> >> Thanks,
> >>
> >> Neil
> >
> >
> >.
> >|||Tibor,
Thanks very much for your help. Creating the data type in
model has been ruled out from above (!) as it would mean
remembering to change it in two places (in the working db
and in model).
I'm curious as to why the procedure should work after the
datatype has been created then dropped in tempdb. I can
see what you are saying about scoping, but why would it be
OK after creating/dropping the type in tempdb? It still
doesn't exist in tempdb after all...
Our current plan is to run a job when SQLAgent starts to
copy the user types from our working db to tempdb (so now
we have to remember to restart the server after a change
to the user types - where will it all end!)
Thanks again for your interest in this problem,
Neil
>--Original Message--
>This is a scoping problem. Since the UDT is created in
dynamic SQL, the datatype doesn't exist when
>SQL Server parses the code in the procedure. Create the
datatype in model instead.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message
>news:244901c4a176$b0fdea60$a601280a@.phx.gbl...
>> It seems I spoke too soon! The following code reproduces
>> the problem:
>> CREATE PROCEDURE MyUDTTest AS
>> BEGIN
>> declare @.stmt as nvarchar(255)
>> set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
>> sp_addtype ''udtBuySell'' , ''char(1)'''
>> exec sp_executesql @.stmt
>> CREATE TABLE #MyTable (MyColumn udtBuySell)
>> END
>> trying to run this SP after a stop/start gives the
>> following error:
>> Server: Msg 2715, Level 16, State 7, Procedure
MyUDTTest,
>> Line 10
>> Column or parameter #1: Cannot find data type
udtBuySell.
>> in tempdb create the udtBuySell data type. Then drop it.
>> Now the sp will run.
>> Any thoughts?
>> Neil
>>
>> >--Original Message--
>> >Seems to be some issue with a user defined datatype not
>> existing in tempdb. Hard to ell without a
>> >repro. Perhaps it is as easy as creating that dt in the
>> model database so you know it will exist in
>> >tempdb?
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> >"Neil" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> I'm having a problem with SQL Server 7. If I restart
the
>> >> server, and the first stroed proc I run contains a
>> >> reference to a user defined data type then the query
>> fails
>> >> because the data type is not recognised. (In this
case
>> I'm
>> >> creating a temporary table in the stored proc, where
one
>> >> column in the table uses the user defined data type)
>> >>
>> >> A workaround is to run another stored proc first, and
>> then
>> >> everything is fine.
>> >>
>> >> Has anyone got a more elegant solution or reason why
>> this
>> >> would happen?
>> >>
>> >> Thanks,
>> >>
>> >> Neil
>> >
>> >
>> >.
>> >
>
>.
>

First connection fails through Access

I am accessing a linked server through a VPN connection. I am updating
through an Access interface. The first time that I perform an update
on the linked server table, I get an update on linked table error. The
new transaction could not enlist in the specified transaction...
After I click OK, I have update and add records with no errors. Is
there a way that I can trap for this error or avoid it all together?Hi
This sounds like a delay introduced by the VPN means it takes longer to
connect first time through. You may want to try forcing a connection earlier
before you try to update. Also check any VPN settings such as timeouts or
keep alive to see if changing them helps.
John
"dgall" wrote:

> I am accessing a linked server through a VPN connection. I am updating
> through an Access interface. The first time that I perform an update
> on the linked server table, I get an update on linked table error. The
> new transaction could not enlist in the specified transaction...
> After I click OK, I have update and add records with no errors. Is
> there a way that I can trap for this error or avoid it all together?
>

First connection fails through Access

I am accessing a linked server through a VPN connection. I am updating
through an Access interface. The first time that I perform an update
on the linked server table, I get an update on linked table error. The
new transaction could not enlist in the specified transaction...
After I click OK, I have update and add records with no errors. Is
there a way that I can trap for this error or avoid it all together?Hi
This sounds like a delay introduced by the VPN means it takes longer to
connect first time through. You may want to try forcing a connection earlier
before you try to update. Also check any VPN settings such as timeouts or
keep alive to see if changing them helps.
John
"dgall" wrote:
> I am accessing a linked server through a VPN connection. I am updating
> through an Access interface. The first time that I perform an update
> on the linked server table, I get an update on linked table error. The
> new transaction could not enlist in the specified transaction...
> After I click OK, I have update and add records with no errors. Is
> there a way that I can trap for this error or avoid it all together?
>

First attempt to connect fails

I have a C# application that connects to a SQL Server Express 2005 instance. One of the testers here shuts his machine down every night and first thing in the morning when he fires up the application it fails to connect. If he tries to open it again right after that it connects. What would the failed attempt do that would fix the instance?

hi,

what is the reported exception?

regards

|||

If the C# application is using User Instances, the problem is likely a timeout. Check the connection string, if it is specifiying User Instance = TRUE, then add 'Connection Timeout=60' and the problem will likely go away. The issue is that a User Instance has to be started when the application is started and depending on hardware, it can take a bit longer than the default connection time out. Once it has started, it will hang around for 60 minutes after the last connection is dropped, so the second time you run the applciation, the User Instance is running and the connection can be made within the default timeout setting.

Mike

|||

The connection string doesn't have user instance in it. Should I just not be using user instances?

|||YOu don′t have to, depending on the machine and the workload it also could be that the database is closed again and will have to open after reconnecting. The autoclose option can be either turned off (turned on by default for Express instances) or the connection timeout can be increased as Mike pointed out.

Jens K. Suessmeyer.

http://www.sqlserver2005.de