Endless running package using a proxy

I have a package which runs fine, when I execute it with my account (e.g. double-click on the .dtsx file and run it).

Now I would like to establish a job, which starts the package. I created first a credential for my Account (which is a domain administrator account also for the box, where SQL Server is running on), then I defined a proxy to this credential.

In the job definition I changed the Run as... to this Proxy (it is a SSIS Proxy) and then I started the job.

Th job does NOT abend, it runs forever! So I have to stop it manually.

In the log I can see as last entry : "operation complete". It stops at a "Execute process task" where I call the bcp utility.

Does anyone has an idea, why a package can run forever?!?!

Regards

Norbert

Sorry, it is not the package running forever (it logged "operation complete) but the job does not stop.

In addition I would like to add another information. I tested the package also as CmdExec Call (dtexec ....) but the result is still the same.

Regards

Norbert Bender

Endian problems with converting to varbinary

We are in the process of migrating from a DOS based binary file storage
format to a relational database structure. However, for migration reasons we
need to be able to export the relational data to the binary file structure
for processing. I have accomplished this with a not-so-lightweight procedure
that basically steps through data, pulls out a variable, converts it to
binary, and appends it to an Image column. At the end of the process I use
textcopy within the TSQL code to write the binary file out. However, when
processing the data in a VB6 utility, reading an integer from the file
becomes impossible. 1's become 256, 2's become 512, 4's become 1024, and
larger values are crazy. Stepping through the original files, we notice that
a one would be stored as 0x10, a 2 as 0x20. Stepping through the file that
SQL Server wrote, we noticed that a one was 0x01, a two was 0x02, etc.
I understand why the old files work, as that is in little endian format,
native to windows. However, I don't understand why SQL server is converting
to a binary structure that looks to be big endian. Below is an example of my
conversion, please let me know what I'm doing wrong and/or how I can fix it
to make it work correctly (tempBlob.MainBlob is the Image that I'm updating)
.
Thanks!
Code:
DECLARE @.bin2 binary(2)
DECLARE @.PixelsAcrossDetector SMALLINT
SELECT @.PixelsAcrossDetector = (SELECT PixelsAcross FROM Strips WHERE GUID =
@.StripGUID)
SELECT @.bin2 = (SELECT CONVERT(BINARY(2), @.PixelsAcrossDetector))
UPDATETEXT tempBlob.MainBlob @.Pointer NULL 0 @.bin2> Stepping through the original files, we notice that
> a one would be stored as 0x10, a 2 as 0x20. Stepping through the file that
> SQL Server wrote, we noticed that a one was 0x01, a two was 0x02, etc.
> I understand why the old files work, as that is in little endian format,
> native to windows. However, I don't understand why SQL server is
> converting
> to a binary structure that looks to be big endian.
Consider the following:
DECLARE
@.PixelsAcrossDetector smallint,
@.bin2 binary(2)
SET @.PixelsAcrossDetector = 1
SET @.bin2 = CONVERT(BINARY(2), @.PixelsAcrossDetector)
SELECT @.bin2
The result is a fixed-length *binary string* with a value of 0x0001 rather
than the little endian smallint value 0x0100 you want to serialize. One
method to address the problem is to swap the bytes like the example below.
It's ugly but Transact-SQL isn't really designed to create binary records.
DECLARE
@.PixelsAcrossDetector smallint,
@.bin2 binary(2),
@.Byte0 binary(1),
@.Byte1 binary(1)
SET @.PixelsAcrossDetector = 1
SET @.bin2 = CONVERT(BINARY(2), @.PixelsAcrossDetector)
SELECT @.Byte0 = SUBSTRING(@.bin2, 2, 1)
SELECT @.Byte1 = SUBSTRING(@.bin2, 1, 1)
SELECT @.Bin2 = @.Byte0 + @.Byte1
SELECT @.Bin2
Hope this helps.
Dan Guzman
SQL Server MVP
"David Harris" <DavidHarris@.discussions.microsoft.com> wrote in message
news:35FE8697-EE20-4641-9042-3F569D5BCDC9@.microsoft.com...
> We are in the process of migrating from a DOS based binary file storage
> format to a relational database structure. However, for migration reasons
> we
> need to be able to export the relational data to the binary file structure
> for processing. I have accomplished this with a not-so-lightweight
> procedure
> that basically steps through data, pulls out a variable, converts it to
> binary, and appends it to an Image column. At the end of the process I use
> textcopy within the TSQL code to write the binary file out. However, when
> processing the data in a VB6 utility, reading an integer from the file
> becomes impossible. 1's become 256, 2's become 512, 4's become 1024, and
> larger values are crazy. Stepping through the original files, we notice
> that
> a one would be stored as 0x10, a 2 as 0x20. Stepping through the file that
> SQL Server wrote, we noticed that a one was 0x01, a two was 0x02, etc.
> I understand why the old files work, as that is in little endian format,
> native to windows. However, I don't understand why SQL server is
> converting
> to a binary structure that looks to be big endian. Below is an example of
> my
> conversion, please let me know what I'm doing wrong and/or how I can fix
> it
> to make it work correctly (tempBlob.MainBlob is the Image that I'm
> updating).
> Thanks!
> Code:
> DECLARE @.bin2 binary(2)
> DECLARE @.PixelsAcrossDetector SMALLINT
> SELECT @.PixelsAcrossDetector = (SELECT PixelsAcross FROM Strips WHERE GUID
> =
> @.StripGUID)
> SELECT @.bin2 = (SELECT CONVERT(BINARY(2), @.PixelsAcrossDetector))
> UPDATETEXT tempBlob.MainBlob @.Pointer NULL 0 @.bin2|||>> We are in the process of migrating from a DOS based binary file storage f
ormat to a relational database structure. <<
Now you know why BIT is not part of the SQL Standards. SQL is meant to
be high-level, abstract language; use files and machine-specific
assembly language for that level of programming.
Right tool for the job; no kludges.|||"--CELKO--" Wrote:
> Now you know why BIT is not part of the SQL Standards.
> SQL is meant to be high-level, abstract language; use
> files and machine-specific assembly language for that
> level of programming.
One of my joys is that this is only interim, until we can rewrite our
processing software to make database calls insted of reading the binary
files. But unfortunately that will be a ways off for now, and we have to
kludge it up right now. :( But good design is right around the corner... :)
"Dan Guzman" wrote:
> The result is a fixed-length *binary string* with a value of 0x0001 rather
> than the little endian smallint value 0x0100 you want to serialize. One
> method to address the problem is to swap the bytes like the example below.
> It's ugly but Transact-SQL isn't really designed to create binary records.
Yeah, I was hoping it was a setting or a separate convert function. :) But
this seems like the best solution, will just have to abstract it to simplify
things. Appreciate the help. :)
Will the swap method also work with doubles/floats, just by scaling the
variables to 8 instead of 2, and completely reversing it? Or must the double
s
have a separate format?|||> Will the swap method also work with doubles/floats, just by scaling the
> variables to 8 instead of 2, and completely reversing it? Or must the
> doubles
> have a separate format?
It gets especially nasty when you deal with single and double data types.
Not only do you need to address the big/little endian issue, you'll need to
create the exponent correctly. It's likely to be easier and faster to write
a custom application program than to do this in Transact-SQL. I don't
always agree with Joe but, IMHO, this is beyond an acceptable temporary
kludge.
Hope this helps.
Dan Guzman
SQL Server MVP
"David Harris" <DavidHarris@.discussions.microsoft.com> wrote in message
news:219FAFF8-546B-47B1-B744-02BFD0391989@.microsoft.com...
> "--CELKO--" Wrote:
> One of my joys is that this is only interim, until we can rewrite our
> processing software to make database calls insted of reading the binary
> files. But unfortunately that will be a ways off for now, and we have to
> kludge it up right now. :( But good design is right around the corner...
> :)
> "Dan Guzman" wrote:
> Yeah, I was hoping it was a setting or a separate convert function. :) But
> this seems like the best solution, will just have to abstract it to
> simplify
> things. Appreciate the help. :)
> Will the swap method also work with doubles/floats, just by scaling the
> variables to 8 instead of 2, and completely reversing it? Or must the
> doubles
> have a separate format?|||>> Now you know why BIT is not part of the SQL Standards.
It was a bit suprising that SQL:99 bought it up & is already deprecated in
SQL:2003
Anith|||"Dan Guzman" wrote:
> It gets especially nasty when you deal with single and double data types.
> Not only do you need to address the big/little endian issue, you'll need t
o
> create the exponent correctly. It's likely to be easier and faster to wri
te
> a custom application program than to do this in Transact-SQL. I don't
> always agree with Joe but, IMHO, this is beyond an acceptable temporary
> kludge.
Agreed. Too bad everything isn't an integer. :) I did manage to create a few
procedures to abstract a good part of the process into TSQL, which cuts the
time at least in half from doing everything from C#. Appreciate the help,
even though it's not exactly what I wanted to hear... But this is interim,
and we'll take just about anything right now. :)

Encryption question

Hello Everybody,
I have a Encryption - Decription Question.
In my project we are getting an XML File from a vendor which has a credit
card number in clear text. We use XML bulk load process to load table from
xml file.
How can i encrypt credit card number while storing into table.
Also i will have to decrypt CC number while i create comma separated file
for another vendor ?
Pls let me know.
thxA credit card number is typically not the type of attribute used for
indexing, sorting, grouping, or composing a primary key, so I see no reason
why it would not be a good candidate for encryption. It would be reasonable
and fairly simple to store it encrypted and decrypt it at the application
level only when needed for display on a customer information form or to
complete a business transaction.
However, a social security number is a different issue.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:81898E3B-37E1-4B90-9F83-2E522A29D064@.microsoft.com...
> Hello Everybody,
> I have a Encryption - Decription Question.
> In my project we are getting an XML File from a vendor which has a credit
> card number in clear text. We use XML bulk load process to load table from
> xml file.
> How can i encrypt credit card number while storing into table.
> Also i will have to decrypt CC number while i create comma separated file
> for another vendor ?
> Pls let me know.
> thx|||I have a few line sof VB.Net code that Encrypts or Decrypts a string passed
to it; If interested then let me know and I will post the code for you.
"mvp" wrote:

> Hello Everybody,
> I have a Encryption - Decription Question.
> In my project we are getting an XML File from a vendor which has a credit
> card number in clear text. We use XML bulk load process to load table from
> xml file.
> How can i encrypt credit card number while storing into table.
> Also i will have to decrypt CC number while i create comma separated file
> for another vendor ?
> Pls let me know.
> thx|||Thanks for the reply
But can i know how can i do this encryption-decryption..
thanks
"JT" wrote:

> A credit card number is typically not the type of attribute used for
> indexing, sorting, grouping, or composing a primary key, so I see no reaso
n
> why it would not be a good candidate for encryption. It would be reasonabl
e
> and fairly simple to store it encrypted and decrypt it at the application
> level only when needed for display on a customer information form or to
> complete a business transaction.
> However, a social security number is a different issue.
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:81898E3B-37E1-4B90-9F83-2E522A29D064@.microsoft.com...
>
>|||I want to read from XML file (which has CC number in clear text, i use sql
bulk load to load xml into table) and load into sql server table in encrypt
form and then decrypt again when i create comma seperated file from table
again to provide feed to another vendor ?
Let me know, do u have similar thing ?
"Shariq" wrote:
> I have a few line sof VB.Net code that Encrypts or Decrypts a string passe
d
> to it; If interested then let me know and I will post the code for you.
> "mvp" wrote:
>|||If your entire input and output processing is done with SQL server then I
haven't done similar thing but you could possibly use ActiveX script to
Encrypt/Decrypt.
In a similiar type of situation when I had to encrypt/decrypt for input and
output; I use VB.Net application to re-process XML file as input, encrypted
the Credit Card numbers and output to an XML file. Then I executed a DTS
package to upload the XML formatted output file to a SQL Server.
When sending data to a client with decrypted CC numbers; I also used the VB
application to rad data from the SQL Server and during the data read,
decrypted the CC numbers and produced XML file.
"mvp" wrote:
> I want to read from XML file (which has CC number in clear text, i use sq
l
> bulk load to load xml into table) and load into sql server table in encry
pt
> form and then decrypt again when i create comma seperated file from table
> again to provide feed to another vendor ?
> Let me know, do u have similar thing ?
> "Shariq" wrote:
>|||Hello Shariq,
I'm Interesting about the encryption/decryption code that you wrote in VB.NE
T.
Would you like to post it me?
Thank you!
"Shariq" wrote:
> If your entire input and output processing is done with SQL server then I
> haven't done similar thing but you could possibly use ActiveX script to
> Encrypt/Decrypt.
> In a similiar type of situation when I had to encrypt/decrypt for input an
d
> output; I use VB.Net application to re-process XML file as input, encrypte
d
> the Credit Card numbers and output to an XML file. Then I executed a DTS
> package to upload the XML formatted output file to a SQL Server.
> When sending data to a client with decrypted CC numbers; I also used the V
B
> application to rad data from the SQL Server and during the data read,
> decrypted the CC numbers and produced XML file.
> "mvp" wrote:
>|||If you are taking about decrypting data coming in from the source data or
encrypting data as it extract to another format, then this would be
implemented at the application level not the database level. It depends on
what application programming tool your are using.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:8470213F-47A3-417A-B920-BB7759AC0738@.microsoft.com...
> Thanks for the reply
> But can i know how can i do this encryption-decryption..
> thanks
> "JT" wrote:
>|||x-rays,
You might be able to better technique but the technique I use works great
for me.
This function will either encrypt or decrypt depanding on what is passed to
it.
Public Function MyCryption(ByVal strInput As String) As String
' Encrypts/decrypts the passed string using
' a simple ASCII value-swapping algorithm
Dim strTempChar As String, i As Integer
For i = 1 To Len(strInput)
If Asc(Mid$(strInput, i, 1)) < 128 Then
strTempChar = CType(Asc(Mid$(strInput, i, 1)) + 128, String)
ElseIf Asc(Mid$(strInput, i, 1)) > 128 Then
strTempChar = CType(Asc(Mid$(strInput, i, 1)) - 128, String)
End If
Mid$(strInput, i, 1) = Chr(CType(strTempChar, Integer))
Next i
Return strInput
End Function
"x-rays" wrote:
> Hello Shariq,
> I'm Interesting about the encryption/decryption code that you wrote in VB.
NET.
> Would you like to post it me?
> Thank you!
> "Shariq" wrote:
>|||Keep in mind that whatever encryption you end up using, the encrypted data
is only as secure as the code you use for the encryption. If your
encryption code and key are stored in a source control system, everyone with
access to that system will be able to decrypt the data, provided they have
access to the table where the data is stored.
If your programmers do not have access to the production database, and your
DBAs do not have access to your application code, then you should be ok.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:81898E3B-37E1-4B90-9F83-2E522A29D064@.microsoft.com...
> Hello Everybody,
> I have a Encryption - Decription Question.
> In my project we are getting an XML File from a vendor which has a credit
> card number in clear text. We use XML bulk load process to load table from
> xml file.
> How can i encrypt credit card number while storing into table.
> Also i will have to decrypt CC number while i create comma separated file
> for another vendor ?
> Pls let me know.
> thx

Encryption Key

I am getting the following error while configuring the reporting services on
my local machine. I cannot restore the key as I do not know the file or the
password.
Somebody please help!
ReportServicesConfigUI.WMIProvider.WMIProviderException: The encrypted value
for the "LogonCred" configuration setting cannot be decrypted.
(rsFailedToDecryptConfigInformation)
at
ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)
at
ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()Hello,
Are you installing a new version of Reporting Services (with clear
ReportServer database) or just reinstalling it with using previous version
of ReportServer database'
It looks like that you've reinstalled Reporting Services without recovering
previous encryption key.
If you don't have any copy of your previous encryption key, the only way to
make Reporting Services content available is to delete all unusable
encrypted data from ReportServer database.
Follow these steps to apply the encryption key to the report server
database:
1.. Run rskeymgmt.exe locally on the computer that hosts the report
server. You must use the -d apply argument. The following example
illustrates the argument you must specify:
rskeymgmt -d
2.. Restart Internet Information Service (IIS).
After the values are removed, you must re-specify the values as follows:
1.. Run rsconfig utility to specify a report server connection. This step
replaces the report server connection information. For more information, see
Configuring a Report Server Connection and rsconfig Utility.
2.. If you are supporting unattended report execution for reports that do
not use credentials, run rsconfig to specify the account used for this
purpose. For more information, see Configuring an Account for Unattended
Report Processing.
3.. For each report and shared data source that uses stored credentials,
you must retype the user name and password. For more information, see
Specifying Credential and Connection Information.
4.. Open and resave each subscription. Subscriptions retain residual
information about the encrypted credentials deleted during the rskeymgmt
delete operation. You can update the subscription by opening and saving it.
You do not need to modify or recreate it.
I hope this infomation will helpful.
Best Regards,
Radoslaw Lebkowski
U¿ytkownik "RouteC" <RouteC@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:D246447F-1622-4230-AC73-71D0F1073C11@.microsoft.com...
>I am getting the following error while configuring the reporting services
>on
> my local machine. I cannot restore the key as I do not know the file or
> the
> password.
> Somebody please help!
> ReportServicesConfigUI.WMIProvider.WMIProviderException: The encrypted
> value
> for the "LogonCred" configuration setting cannot be decrypted.
> (rsFailedToDecryptConfigInformation)
> at
> ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject
> mo)
> at
> ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()|||Hi Rad,
The very first step in trying to delete the key is giving me the same
exception which is ReportServicesConfigUI.WMIProvider.WMIProviderException:
The encrypted value
for the "LogonCred" configuration setting cannot be decrypted.
rskeymgmt -d -i instance
This is completely new installation in 2steps.
1. Installed sql server 2005 with all other services except reporting
services as iis was not installed at that time.
2. Installed reporting services.
"Radoslaw Lebkowski" wrote:
> Hello,
> Are you installing a new version of Reporting Services (with clear
> ReportServer database) or just reinstalling it with using previous version
> of ReportServer database'
> It looks like that you've reinstalled Reporting Services without recovering
> previous encryption key.
> If you don't have any copy of your previous encryption key, the only way to
> make Reporting Services content available is to delete all unusable
> encrypted data from ReportServer database.
> Follow these steps to apply the encryption key to the report server
> database:
> 1.. Run rskeymgmt.exe locally on the computer that hosts the report
> server. You must use the -d apply argument. The following example
> illustrates the argument you must specify:
> rskeymgmt -d
> 2.. Restart Internet Information Service (IIS).
> After the values are removed, you must re-specify the values as follows:
> 1.. Run rsconfig utility to specify a report server connection. This step
> replaces the report server connection information. For more information, see
> Configuring a Report Server Connection and rsconfig Utility.
> 2.. If you are supporting unattended report execution for reports that do
> not use credentials, run rsconfig to specify the account used for this
> purpose. For more information, see Configuring an Account for Unattended
> Report Processing.
> 3.. For each report and shared data source that uses stored credentials,
> you must retype the user name and password. For more information, see
> Specifying Credential and Connection Information.
> 4.. Open and resave each subscription. Subscriptions retain residual
> information about the encrypted credentials deleted during the rskeymgmt
> delete operation. You can update the subscription by opening and saving it.
> You do not need to modify or recreate it.
> I hope this infomation will helpful.
> Best Regards,
> Radoslaw Lebkowski
>
> U¿ytkownik "RouteC" <RouteC@.discussions.microsoft.com> napisa³ w wiadomo¶ci
> news:D246447F-1622-4230-AC73-71D0F1073C11@.microsoft.com...
> >I am getting the following error while configuring the reporting services
> >on
> > my local machine. I cannot restore the key as I do not know the file or
> > the
> > password.
> > Somebody please help!
> >
> > ReportServicesConfigUI.WMIProvider.WMIProviderException: The encrypted
> > value
> > for the "LogonCred" configuration setting cannot be decrypted.
> > (rsFailedToDecryptConfigInformation)
> > at
> > ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject
> > mo)
> > at
> > ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()
>
>|||Hmm, that's a little strange situation.
Under the following link there is a very similar problem with LogonCred
decryption problem.
http://groups.google.pl/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/ec7858be1ef25e24/9f72570a671b74e7?lnk=st&q=LogonCred+decrypted&rnum=1&hl=pl#9f72570a671b74e7
Maybe you have problem with authorization a connection to ReportServer
database (bad values contained in RSReportServer.config file used to connect
to the report server database)
Try to use rsconfig utility described below
http://msdn2.microsoft.com/en-us/library/aa179654(SQL.80).aspx
Regards
Radoslaw Lebkowski
U¿ytkownik "RouteC" <RouteC@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:7BC3B705-71DF-4B48-93FE-6959E94A51FF@.microsoft.com...
> Hi Rad,
> The very first step in trying to delete the key is giving me the same
> exception which is
> ReportServicesConfigUI.WMIProvider.WMIProviderException:
> The encrypted value
> for the "LogonCred" configuration setting cannot be decrypted.
> rskeymgmt -d -i instance
> This is completely new installation in 2steps.
> 1. Installed sql server 2005 with all other services except reporting
> services as iis was not installed at that time.
> 2. Installed reporting services.
> "Radoslaw Lebkowski" wrote:
>> Hello,
>> Are you installing a new version of Reporting Services (with clear
>> ReportServer database) or just reinstalling it with using previous
>> version
>> of ReportServer database'
>> It looks like that you've reinstalled Reporting Services without
>> recovering
>> previous encryption key.
>> If you don't have any copy of your previous encryption key, the only way
>> to
>> make Reporting Services content available is to delete all unusable
>> encrypted data from ReportServer database.
>> Follow these steps to apply the encryption key to the report server
>> database:
>> 1.. Run rskeymgmt.exe locally on the computer that hosts the report
>> server. You must use the -d apply argument. The following example
>> illustrates the argument you must specify:
>> rskeymgmt -d
>> 2.. Restart Internet Information Service (IIS).
>> After the values are removed, you must re-specify the values as follows:
>> 1.. Run rsconfig utility to specify a report server connection. This
>> step
>> replaces the report server connection information. For more information,
>> see
>> Configuring a Report Server Connection and rsconfig Utility.
>> 2.. If you are supporting unattended report execution for reports that
>> do
>> not use credentials, run rsconfig to specify the account used for this
>> purpose. For more information, see Configuring an Account for Unattended
>> Report Processing.
>> 3.. For each report and shared data source that uses stored
>> credentials,
>> you must retype the user name and password. For more information, see
>> Specifying Credential and Connection Information.
>> 4.. Open and resave each subscription. Subscriptions retain residual
>> information about the encrypted credentials deleted during the rskeymgmt
>> delete operation. You can update the subscription by opening and saving
>> it.
>> You do not need to modify or recreate it.
>> I hope this infomation will helpful.
>> Best Regards,
>> Radoslaw Lebkowski
>>
>> U?ytkownik "RouteC" <RouteC@.discussions.microsoft.com> napisa3 w
>> wiadomo?ci
>> news:D246447F-1622-4230-AC73-71D0F1073C11@.microsoft.com...
>> >I am getting the following error while configuring the reporting
>> >services
>> >on
>> > my local machine. I cannot restore the key as I do not know the file
>> > or
>> > the
>> > password.
>> > Somebody please help!
>> >
>> > ReportServicesConfigUI.WMIProvider.WMIProviderException: The encrypted
>> > value
>> > for the "LogonCred" configuration setting cannot be decrypted.
>> > (rsFailedToDecryptConfigInformation)
>> > at
>> > ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject
>> > mo)
>> > at
>> > ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()
>>

Encryption in MDF file possible?

I am working on a distributed application that will use a SQL Express 2005 MDF file for the primary application data storage. The program will be storing sensitive data and I would like to encrypt the data in it.

I have searched through the forums and cannot find any reference to how to enable encryption for an MDF file.

I thought about writing my own encrypt / decrypt functions, however, I'm using databound controls and do not have a home-grown data access layer that I can tap into to implement the encryption.

When I originally added the MDF file to my application, under the "Advanced" settings, I saw that I could switch an "Encryption" property to "True"... figuring that would do the trick. However, when the MDF file is created I get an error stating, "The instance of SQL Server you attempted to connect to does not support encryption".

What is the trick for encrypting data in an MDF file that is being used with databound controls? Remember that this will be a distributed app - so if there are any customizations that are required on the SQL Express side to support this - I will need to find some way to configure SQL Express on the target machines during my bootstrap install of it.

Any help would be greatly appreciated! Thanks!

I think you actually want to encrypt the data and not the file. Here is a link to a starting point:

http://msdn2.microsoft.com/en-us/library/ms190357.aspx

|||

Yes - that is what I meant... encrypting *data* in the MDF file.

As I wrote in my original post, I'm using data-bound controls, so I do not have a data layer that have created to pass data through - it is all being handled by the magic of Microsoft. That being said, I'm not sure how to implement the information contained in the link you provided. That seemed in line with me writing my own encryption functions and passing the data through them during read / writing to the database - which isn't an option for me using databound controls. Or is it?

|||

I've never tried using the the SQL Server 2005 encryption with databound controls but it should work as long as you can edit the query. You basically extend the query to include encryption and decryption information.

The pointer I sent you is just one of several BTW

Encrypting the configuration file values stored in SQL server

Hi All,

I have the following requirement. I need to store the password for the connection manager in the configuration file. The sink for the configuration file is SQL Server. Though the password field appears as "******" the actual value is being taken as ""******" itself. If i update the SQL server table with the correct value, then the package starts working. But, the password is shown as clear text.

If i write logic to encrypt the password column in the configuration table, is there a way to tell the SSIS execute engine to decrypt the password before using the same for making the connection.

Is there a place holder, where i can write the decrypt code so that the decrypted password can be sent to the execution engine?

Thanks In Advance,

Madhu

I think the short answer to this is no, and no code hooks either.

I think though that there is also an argument, that says it would not be more secure than what you have now. If you encrypt the data, you need to then secure the key. So what will you do to secure the key? Why not use strong security to secure the password data instead of worrying about how to secure the key? I accept that the encryption adds an extra step, but I'm not convinced it will actually be any safer.

|||I'm not sure if it's a good idea, but couldn't he create a script task to decrypt the password and reset the connection manager's connectionstring property before the connection manager is used in the package?|||

Yes and no. Some connections are used before your script task could run, such as connections used for logging.

How would you secure the key used to decrypt the password? You need to secure the encryption/decryption key, so why not just secure the password to start with?

|||DarrenSQLIS is right the recommended way to do this is to store the password in the connection. SSIS will automatically encrypt these so that they are not stored in cleartext.|||Thanks for the thoughts Darren. As suggested by you, way to go is to store the password in SQL server and make sure that the access to the configuration table is only for administrators.|||

Denise, I think you are talking about the package level encryption, protection levels and such like. Nice though it is, it is not very useful, as I think you should "externalise" any kind of security information.

Using package encryption becomes unfeasible when you have to migrate packages between environments. Configurations solve that migration issue, but don't give you the encryption that is often seen as a requirement for some organisations. I'd argue that is should not be a big deal, secure the password so you don't have to worry about the key, but often it is an internal "standard" that must be complied with.

Still we have the choice of package encryption, which is better than not!

Encrypting the actual MDF file - not the data column

Hi there,
We have a website application that we needed to replicae in various laptops
for our team that does not have internet access to our production site
constantly. We wrote the application that keeps in sync the website, the
database and the relevant data between the laptop and our production server.
In order to do that, we had to replicate the production database and
production "compiled" website on the local laptop, and have the applicaion
downloading and uploading the relevant data in order to keep the whole thing
in sync. Success !!!
Now comes the problem:
At this point we have a laptop that holds a production compiled dotfuscated
website (we are ok with that) and the production database with a minimum
subset of data. This means that the Database schema and the data is open to
anybody that has access to that laptop. So what happen if the laptop gets
stolen ?
The only way we can allow that is if we find a way to encrypt somehow the
MDF file.
Constraint:
We cannot change the production website and SP code to ENCRYPT - DECRYPT
certain data columns.
So my question is ..can I secure the DB file in case the laptop get stolen?
Thanks,
FPConsider using Encrypting File System (EFS).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:%23QK1780ZHHA.5020@.TK2MSFTNGP05.phx.gbl...
Hi there,
We have a website application that we needed to replicae in various laptops
for our team that does not have internet access to our production site
constantly. We wrote the application that keeps in sync the website, the
database and the relevant data between the laptop and our production server.
In order to do that, we had to replicate the production database and
production "compiled" website on the local laptop, and have the applicaion
downloading and uploading the relevant data in order to keep the whole thing
in sync. Success !!!
Now comes the problem:
At this point we have a laptop that holds a production compiled dotfuscated
website (we are ok with that) and the production database with a minimum
subset of data. This means that the Database schema and the data is open to
anybody that has access to that laptop. So what happen if the laptop gets
stolen ?
The only way we can allow that is if we find a way to encrypt somehow the
MDF file.
Constraint:
We cannot change the production website and SP code to ENCRYPT - DECRYPT
certain data columns.
So my question is ..can I secure the DB file in case the laptop get stolen?
Thanks,
FP|||Tom,
With EFS, would my local application be able to connect and run queries
normally?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Consider using Encrypting File System (EFS).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
> news:%23QK1780ZHHA.5020@.TK2MSFTNGP05.phx.gbl...
> Hi there,
> We have a website application that we needed to replicae in various
> laptops
> for our team that does not have internet access to our production site
> constantly. We wrote the application that keeps in sync the website, the
> database and the relevant data between the laptop and our production
> server.
> In order to do that, we had to replicate the production database and
> production "compiled" website on the local laptop, and have the applicaion
> downloading and uploading the relevant data in order to keep the whole
> thing
> in sync. Success !!!
> Now comes the problem:
> At this point we have a laptop that holds a production compiled
> dotfuscated
> website (we are ok with that) and the production database with a minimum
> subset of data. This means that the Database schema and the data is open
> to
> anybody that has access to that laptop. So what happen if the laptop gets
> stolen ?
> The only way we can allow that is if we find a way to encrypt somehow the
> MDF file.
> Constraint:
> We cannot change the production website and SP code to ENCRYPT - DECRYPT
> certain data columns.
> So my question is ..can I secure the DB file in case the laptop get
> stolen?
> Thanks,
> FP
>
>|||Also,
Since I do need to keep in sync the local DB and sometimes I need to "drop"
it and then recreate an empty one, would I be able to programmatically
encrypt the file using EFS?
If so, do you have a code sample (possibly in C#)?
Thanks again,
Filippo
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:uar7Yq1ZHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Tom,
> With EFS, would my local application be able to connect and run queries
> normally?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
>|||You'd have SQL running under a domain account and that account would then be
writing files (data and log) on the disk. If you copy files to be attached
or restores, be sure to use that same domain account.
How you connect to SQL Server doesn't matter. The login that you use to
talk to SQL Server isn't the account that writes to those files. The SQL
Server service account is.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:uar7Yq1ZHHA.4872@.TK2MSFTNGP03.phx.gbl...
Tom,
With EFS, would my local application be able to connect and run queries
normally?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Consider using Encrypting File System (EFS).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
> news:%23QK1780ZHHA.5020@.TK2MSFTNGP05.phx.gbl...
> Hi there,
> We have a website application that we needed to replicae in various
> laptops
> for our team that does not have internet access to our production site
> constantly. We wrote the application that keeps in sync the website, the
> database and the relevant data between the laptop and our production
> server.
> In order to do that, we had to replicate the production database and
> production "compiled" website on the local laptop, and have the applicaion
> downloading and uploading the relevant data in order to keep the whole
> thing
> in sync. Success !!!
> Now comes the problem:
> At this point we have a laptop that holds a production compiled
> dotfuscated
> website (we are ok with that) and the production database with a minimum
> subset of data. This means that the Database schema and the data is open
> to
> anybody that has access to that laptop. So what happen if the laptop gets
> stolen ?
> The only way we can allow that is if we find a way to encrypt somehow the
> MDF file.
> Constraint:
> We cannot change the production website and SP code to ENCRYPT - DECRYPT
> certain data columns.
> So my question is ..can I secure the DB file in case the laptop get
> stolen?
> Thanks,
> FP
>
>|||You wouldn't encrypt it programmatically. It's done automatically.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:O5Il8p%23ZHHA.1300@.TK2MSFTNGP02.phx.gbl...
Also,
Since I do need to keep in sync the local DB and sometimes I need to "drop"
it and then recreate an empty one, would I be able to programmatically
encrypt the file using EFS?
If so, do you have a code sample (possibly in C#)?
Thanks again,
Filippo
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:uar7Yq1ZHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Tom,
> With EFS, would my local application be able to connect and run queries
> normally?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
>|||Also have a look at:
http://technet.microsoft.com/en-us/.../aa906017.aspx, for a new
technology that would be helpful in this scenario.
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23DmRu$MaHHA.4552@.TK2MSFTNGP05.phx.gbl...
> You'd have SQL running under a domain account and that account would then
> be
> writing files (data and log) on the disk. If you copy files to be
> attached
> or restores, be sure to use that same domain account.
> How you connect to SQL Server doesn't matter. The login that you use to
> talk to SQL Server isn't the account that writes to those files. The SQL
> Server service account is.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
> news:uar7Yq1ZHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Tom,
> With EFS, would my local application be able to connect and run queries
> normally?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
>

encrypting a text file

I have programs written in sas. The programs themselves can be treated like a text file for this purpose. I need a program that will encrypt my program then when needed decyrypt it storing it into a temp file, run it and then delete it.

any thoughts or anything out there to use.

NicoleAny ability to decrypt means potential weakness thus vulnerability. Look into SQLShield, it may help.

Encrypted File System ?

Any comments on SQL 2000 Encrypted File System ?Hi
Microsoft supports it, but it does affect performance, and if you reset the
SQL Server Service Account's profile, the certificate gets invalidated, and
you loose access to the encrypted parts of the disk.
Really do a lot of reading on how the OS implements it before you use it in
production. technet has a lot of information.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob Castleman" <nomail@.here> wrote in message
news:#Q7EHr#LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> Any comments on SQL 2000 Encrypted File System ?
>

Encrypted File System

Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
files as a security practice? Thanks to eveyone for being there to help."coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
> Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
> files as a security practice? Thanks to eveyone for being there to help.
Don't do it!!! It cripples performance.
The biggest slow down in the database is File I/O. By encrypting the data
files, you are in essence significantly increasing the File I/O.
Follow some networking best practices to secure your server and it's data
files. Use encryption over the network (if you must).
Any of these are far better than encrypting the data files.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for the response Rick.
"Rick Sawtell" wrote:
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
> > Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
> > files as a security practice? Thanks to eveyone for being there to help.
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Hi
And once you change the password of the service account, in the AD user
manager, your certificate gets cancelled and your data is no longer
accessible.
If a hacker is on your server, EFS does not help much anymore as he owns
your network already.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23eKmvwkhFHA.572@.TK2MSFTNGP15.phx.gbl...
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>> Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
>> files as a security practice? Thanks to eveyone for being there to help.
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the
> data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||What do you think about column level encryption? I'm considering using
column-level encryption on a few columns of particularly sensitive data in a
database.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23eKmvwkhFHA.572@.TK2MSFTNGP15.phx.gbl...
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>> Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
>> files as a security practice? Thanks to eveyone for being there to help.
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the
> data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Encrypted File System

Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
files as a security practice? Thanks to eveyone for being there to help."coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
> Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
> files as a security practice? Thanks to eveyone for being there to help.
Don't do it!!! It cripples performance.
The biggest slow down in the database is File I/O. By encrypting the data
files, you are in essence significantly increasing the File I/O.
Follow some networking best practices to secure your server and it's data
files. Use encryption over the network (if you must).
Any of these are far better than encrypting the data files.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for the response Rick.
"Rick Sawtell" wrote:

> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the dat
a
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Hi
And once you change the password of the service account, in the AD user
manager, your certificate gets cancelled and your data is no longer
accessible.
If a hacker is on your server, EFS does not help much anymore as he owns
your network already.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23eKmvwkhFHA.572@.TK2MSFTNGP15.phx.gbl...
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the
> data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||What do you think about column level encryption? I'm considering using
column-level encryption on a few columns of particularly sensitive data in a
database.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23eKmvwkhFHA.572@.TK2MSFTNGP15.phx.gbl...
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the
> data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Encrypted File System

Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
files as a security practice? Thanks to eveyone for being there to help.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
> Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
> files as a security practice? Thanks to eveyone for being there to help.
Don't do it!!! It cripples performance.
The biggest slow down in the database is File I/O. By encrypting the data
files, you are in essence significantly increasing the File I/O.
Follow some networking best practices to secure your server and it's data
files. Use encryption over the network (if you must).
Any of these are far better than encrypting the data files.
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks for the response Rick.
"Rick Sawtell" wrote:

> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||Hi
And once you change the password of the service account, in the AD user
manager, your certificate gets cancelled and your data is no longer
accessible.
If a hacker is on your server, EFS does not help much anymore as he owns
your network already.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23eKmvwkhFHA.572@.TK2MSFTNGP15.phx.gbl...
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the
> data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||What do you think about column level encryption? I'm considering using
column-level encryption on a few columns of particularly sensitive data in a
database.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23eKmvwkhFHA.572@.TK2MSFTNGP15.phx.gbl...
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the
> data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Encrypt/Decrypt SQL Server 2005 data files

We are trying to encrypt/decrypt a SQL Server 2005 database file.
It is my understanding that you can encrypt the main database, but not
its log file. The database file was successfully encrypted, but SQL
Server failed to decrypt it on opening after a many minutes delay. The
database was subsequently decrypted with a manual command, but the
database had been damaged and couldn't be re-opened. It had to be
deleted and restored.
It appears that there is no practical way to use an encrypted SQL
database because of apparent glitches and the extremely slow decryption
process.
We have considered backing up the database, encrypting the backup copy,
deleting the database from the SQL directory on shutdown and then
restoring it on startup. Another alternative is to store the data on
removable media.
I would greatly appreciate a suggestion as to how to best protect the
data. We use SecuriKey to protect OS system startup. This works, but it
doesn't protect the data if, for example, the hard drive is moved to
another computer.
I have read the following article:
[url]http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx[/url
]
Thank you very much.
Robert RobinsonHave you looked at using Windows Encrypted File System? That's the supported
way of protecting your data at the filesystem level. There are a few things
to be careful with paticularly with login/permissions management when
encrypting the folder but it's not rocket science (and well document in
msdn/technet).
As for losing the drive, well, not much you can do there really. Even if you
encrypt the filesystem, that generally just delays the would-be thief. When
you lose the hardware, pretty much all bets are off. If you're thinking of
notebooks, you can implement both EFS and secure the hard disk with a
password (go to setup when you boot). That makes is REALLY hard to get
through and will probably buy you enough time to initiate all kinds of
remedial defense actions (e.g. place credit alerts, cancel credit cards,
update resume & post on monster.com, etc...) before they get to your data.
joe.
"Robert Robinson" <robbiex@.bellsouth.net> wrote in message
news:e9fdHUdDHHA.3660@.TK2MSFTNGP06.phx.gbl...
> We are trying to encrypt/decrypt a SQL Server 2005 database file.
> It is my understanding that you can encrypt the main database, but not its
> log file. The database file was successfully encrypted, but SQL Server
> failed to decrypt it on opening after a many minutes delay. The database
> was subsequently decrypted with a manual command, but the database had
> been damaged and couldn't be re-opened. It had to be deleted and restored.
> It appears that there is no practical way to use an encrypted SQL database
> because of apparent glitches and the extremely slow decryption process.
> We have considered backing up the database, encrypting the backup copy,
> deleting the database from the SQL directory on shutdown and then
> restoring it on startup. Another alternative is to store the data on
> removable media.
> I would greatly appreciate a suggestion as to how to best protect the
> data. We use SecuriKey to protect OS system startup. This works, but it
> doesn't protect the data if, for example, the hard drive is moved to
> another computer.
> I have read the following article:
> [url]http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx[/u
rl]
> Thank you very much.
> Robert Robinson|||Hi Joe,
Thank you very much for the reply. EFS is what we tried. There are two
unfortunate limitations. First, according to Microsoft, you cannot use
SQL if the log file is encrypted. Second, decrypt takes many minutes and
the long required time makes the technology impractical to use.
I agree that there is no absolute way to prevent access to data once an
expert has physical possession of a computer or a hard drive.
SecuriKey does work as advertised. There are ways to circumvent the
technology, but it provides some protection.
Robbie|||> Thank you very much for the reply. EFS is what we tried. There are two
> unfortunate limitations. First, according to Microsoft, you cannot use SQL
> if the log file is encrypted. Second, decrypt takes many minutes and the
> long required time makes the technology impractical to use.
> I agree that there is no absolute way to prevent access to data once an
> expert has physical possession of a computer or a hard drive.
> SecuriKey does work as advertised. There are ways to circumvent the
> technology, but it provides some protection.
Maybe you can encrypt just the snsitive part of the data? Try to look at the
EncryptByKey and other encryption functions in BOL. Together with carefully
set NTFS permissions and encrypted backup you might get what you need.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||Hi Dejan,
Thank you very much for the suggestions.
Robbie
Dejan Sarka wrote:
> Maybe you can encrypt just the snsitive part of the data? Try to look at t
he
> EncryptByKey and other encryption functions in BOL. Together with carefull
y
> set NTFS permissions and encrypted backup you might get what you need.
>|||We decided on the following to provide a reasonable level of protection.
First, computer access is limited by using the SecuriKey.
SQL database files are protected as follows:
Setup
1. The SQL databases to be protected are backed up and are then deleted
from SQL Server.
2. PGP Desktop 9.5 is used to create a new Virtual Disk.
3. This disk is mounted.
4. A new SQL database is created with its data and log files assigned to
be resident on the virtual disk.
5. The data are restored from the backup file.
Start-Up
1. The virtual disk is mounted automatically on start-up or under manual
or programmatic control.
2. A PGP passphrase is entered manually.
3. The SQL database is attached.
Shut-Down
1. The SQL database is detached.
2. The virtual disk is unmounted under manual or programmatic control.
Note that the attach/detach steps are required because SQL Server locks
access to the Log files and the virtual disk cannot not be unmounted
until this lock is released.|||"Robert Robinson" <robbiex@.bellsouth.net> wrote in message
news:uibht4IEHHA.3600@.TK2MSFTNGP06.phx.gbl...
> We decided on the following to provide a reasonable level of protection.
> First, computer access is limited by using the SecuriKey.
> SQL database files are protected as follows:
> Setup
> 1. The SQL databases to be protected are backed up and are then deleted
> from SQL Server.
Are you concerned that fragments of unencrypted data might be lying around
on the storage device even after deletion? Just curious. Thanks.|||Hi Mike,
We are interested in providing a reasonable level of protection for
laptop data. The backup file is created on a server and doesn't have to
be installed on a laptop. The data can be transferred by LAN or
removable media. Your point is, however, well taken. There is no way to
absolutely delete data from a hard drive short of physical destruction
of the platters.
On a slightly different subject, we have run into some interesting
issues involved in using SQL Server data files that are resident in an
encrypted disk volume.
SQL Server locks a database's log file and it is not possible to unmount
a "secure" volume without first releasing this lock. The lock can be
released by an ALTER DATABASE <its name> SET OFFLINE followed by
sp_detach_db.
The database is attached by a SQL script as follows:
Use Master
GO
EXEC sp_attach_db @.dbname = N'database name',
@.filename1 = N'S:\SQLServerData\database name.mdf',
@.filename2 = N'S:\SQLServerData\database name_log.ldf'
GO
The script is executed by:
Shell("sqlcmd -i C:\AttachDetach.sql -U <owner name> -P
<password> -s <server name>")
One interesting glitch is that the above command fails if the owner
name/password precedes the command file.
Another issue is that one needs to know what is shutting down the
application program that is accessing the database. For example, it
might be a normal program exit, a logoff, a battery low warning, or a
system suspend or shutdown.
We had to do some hunting to find the appropriate events. The following
are helpful: Microsoft.Win32.SystemEvents.SessionEnding,
Microsoft.Win32.SystemEvents.PowerModeChanged
and an interesting control called sysinfo.ocx.
Robbie

Encrypt Passwords

I would like to put all my sa and other passwords into a
text-like file and save it out on the network w/o fear
that it can be cracked. Can someone suggest some (and
reasonable) third party tools for something basic like
this?
You may want to look at using something like Whisper 32.
It's free and available at:
http://www.ivory.org/index.html
-Sue
On Tue, 20 Jul 2004 13:32:07 -0700, "Blnt"
<anonymous@.discussions.microsoft.com> wrote:

>I would like to put all my sa and other passwords into a
>text-like file and save it out on the network w/o fear
>that it can be cracked. Can someone suggest some (and
>reasonable) third party tools for something basic like
>this?

Encrypt Passwords

I would like to put all my sa and other passwords into a
text-like file and save it out on the network w/o fear
that it can be cracked. Can someone suggest some (and
reasonable) third party tools for something basic like
this?You may want to look at using something like Whisper 32.
It's free and available at:
http://www.ivory.org/index.html
-Sue
On Tue, 20 Jul 2004 13:32:07 -0700, "Blnt"
<anonymous@.discussions.microsoft.com> wrote:
>I would like to put all my sa and other passwords into a
>text-like file and save it out on the network w/o fear
>that it can be cracked. Can someone suggest some (and
>reasonable) third party tools for something basic like
>this?

Encrypt Passwords

I would like to put all my sa and other passwords into a
text-like file and save it out on the network w/o fear
that it can be cracked. Can someone suggest some (and
reasonable) third party tools for something basic like
this?You may want to look at using something like Whisper 32.
It's free and available at:
http://www.ivory.org/index.html
-Sue
On Tue, 20 Jul 2004 13:32:07 -0700, "Blnt"
<anonymous@.discussions.microsoft.com> wrote:

>I would like to put all my sa and other passwords into a
>text-like file and save it out on the network w/o fear
>that it can be cracked. Can someone suggest some (and
>reasonable) third party tools for something basic like
>this?

Encrypt and save in a file

Hi,
I want to save my business objects in an encrypted XML file. WHat is the
best way to do it? Examples and links are welcome.
Thank you,
SashaWrong group. I am sorry!
"Sasha" <noemail@.noemail.com> wrote in message
news:ub6fKd%23CEHA.3804@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to save my business objects in an encrypted XML file. WHat is the
> best way to do it? Examples and links are welcome.
> Thank you,
> Sasha
>

Encounter Error when FTP get file using tsql

Hi,
I have tried to create my procedure by cut and paste the sample FTP get file
using tsql from Author Nigel Rivett (
http://www.nigelrivett.net/FTP/s_ftp_GetFile.html).
When I tried to execute it by passing parameter as below:
exec s_ftp_GetFile
@.FTPServer = 'www.mysite.com' ,
@.FTPUser = 'mysite' ,
@.FTPPWD = 'xxxxxxx' ,
@.FTPPath = '' ,
@.FTPFileName = 'chat.asp' ,
@.SourcePath = 'D:\wwwusr\mysite\web' ,
@.SourceFile = 'chat.asp' ,
@.workdir = 'c:\Temp'
I encounter error ouput as below
1 User (mysite.comnone)): open www.mysite.com
2 Error opening local file D:\wwwusr\mysite\web\chat.asp.
3 NULL
4 NULL
5 NULL
6 get chat.asp D:\wwwusr\mysite\web\chat.asp
7 quit
8 > D:\wwwusr\mysite\web\chat.asp:Permission denied
9 NULL
Please advise...
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...tivity/200608/1Hi, Thank for all the reply..my problem solved, is due to wrong parameter
passing.
exec s_ftp_GetFile
@.FTPServer = 'www.mysite.com' ,
@.FTPUser = 'mysite' ,
@.FTPPWD = 'xxxxxxx' ,
@.FTPPath = '' , --Should specify the file directoty at Remote Server
@.FTPFileName = 'chat.asp' ,
@.SourcePath = 'C:\myftpfile', -- The source path should be the path the fil
e
will be stored in our local machine
@.SourceFile = 'chat.asp' ,
@.workdir = 'c:\Temp'
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...tivity/200608/1

Encoding string from MS SQL

Hi all,

I have an application which will send out email in plain text in multi langauage.
the email content will be pull from txt file save in UTF-8.
i can send out email from the template with the encoding.
but when i insert data from the SQl server. the data from the SQL server are not encoded.
how do i encode the data (in other lanagauge) from sql server into UTF-8 so that it can be send together with the template.

I have try changing the data into byte and encode it in UTF-8.
but it won't displayed correctly. pls help. thanks

Unicode in SQL Server is either UCS-2 or UTF 16 and the later is the generally used version, .NET is UTF 16 by default so you can change your .NET encoding to UTF 16. The reason is NChar, NVarchar, NText, NChar max and NVarchar max are multi bytes by definition so you just need to convert to UTF 8 in your code. Hope this helps.|||

my data from the database look like this ??òú??. it is chinese simplified gb2313
the datatype for the fields is varchar.
so how do i encode it into UTF8 and it can be display in chinese.
pls help i'm totally confuse by the encoding.

|||

The Chinese you are using is Windows code page, there are six Chinese collation in SQL Server you have to find the right one in the thread below. And to UTF 8 encode in VS you start at the link below it is for VS2003 but I think it should work. So you do column level collation for your specific Chinese in SQL Server and do Unicode encoding in VS and it may be resolved. Hope this helps.

http://forums.asp.net/1067798/ShowPost.aspx

http://www.aspnetresources.com/blog/unicode_in_vsnet.aspx

|||

Hi Thanks for you advice,

base on your info i manage to find the extended proc to solve my prob xp_cp2u_web.
but i have another prob. in my stored procedure the output parameter i set it to a size of 20 data type nvarchar. it will return the result. and it will display correctly on screen. but when it is use to send via email. the rest of the content in the email is gone after my chinese character.

after some debugging, i found that it is due to the size of nvarchar i set. my chinese character size is 3. so if i set the nvarchar size to 3 all will work but if the size i set is bigger than the actual result return. it will affect the rest of the text in my email..

can anyone pls advise me on how to set the nvarchar size for my output sqlparameter.
is there a way where i can set the size of the Nvarchar as dynamic. Thanks

cmd5.CommandText = "GetEmailDetail";
cmd5.CommandType = CommandType.StoredProcedure;
cmd5.Parameters.Add("@.No", Service_ID);
SqlParameter parameterfullname = cmd5.Parameters.Add("@.name", SqlDbType.NVarChar, 20);
parameterfullname.Direction = ParameterDirection.Output;

|||

Try the link below everything you need is covered including the correct stored procedure because that is important. When you are getting value back from a SQL Server stored procedure it is OUTPUT parameter except INT which is return value, so if in doubt always use OUTPUT if it is not needed SQL Server will ignore it. So use correct column level collation and ADO.NET OUTPUT parameters. Hope this helps.

http://msdn.microsoft.com/msdnmag/issues/05/05/DataPoints/

encoding iso-8859-1

Hi Folks
to accomodate an xml file that's encoded iso-8859-1, what do I need to
change in my .xsd code (below):
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
it looks like XMLSchema.xsd is 'wired' as utf-8. (I tried copying
XMLSchema.xsd, XMLSchema.DTD, datatypes.DTD locally and changing the encoding
in XMLSchema.xsd to iso-8859-1, but this causes other problems with simple
data type validations.)
thanks Rob
Did you add the XML declaration:
<?xml version="1.0" encoding="iso-8859-1"?>
in the beginning?
Best regards
Michael
"RobKaratzas" <RobKaratzas@.discussions.microsoft.com> wrote in message
news:1EF8BE76-F750-47D4-BA3C-6F785744C5B5@.microsoft.com...
> Hi Folks
> to accomodate an xml file that's encoded iso-8859-1, what do I need to
> change in my .xsd code (below):
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> --
> it looks like XMLSchema.xsd is 'wired' as utf-8. (I tried copying
> XMLSchema.xsd, XMLSchema.DTD, datatypes.DTD locally and changing the
> encoding
> in XMLSchema.xsd to iso-8859-1, but this causes other problems with simple
> data type validations.)
> thanks Rob
>
|||Hi Michael
I added this to the first line (in both the .xsd AND the .XML file). The
..XML file validates just fine, however, the .xsd (which I've copied locally,
with the DTDs) comes up with:
C:\Inetpub\wwwroot\XMLSchema.xsd(1359): Restriction of anySimpleType is not
allowed. An error occurred at C:\Inetpub\wwwroot\XMLSchema.xsd, (1359, 4).
C:\Inetpub\wwwroot\XMLSchema.xsd(1381): Restriction of anySimpleType is not
allowed. An error occurred at C:\Inetpub\wwwroot\XMLSchema.xsd, (1381, 4).
C:\Inetpub\wwwroot\XMLSchema.xsd(1400): Restriction of anySimpleType is not
allowed. An error occurred at C:\Inetpub\wwwroot\XMLSchema.xsd, (1400, 4).
C:\Inetpub\wwwroot\XMLSchema.xsd(1424): Restriction of anySimpleType is not
allowed. An error occurred at C:\Inetpub\wwwroot\XMLSchema.xsd, (1424, 4).
(The SimpleType definitions fail to validate.)
For the time being, there are only several instances in the XML files where
these characters appear (I think this is more of an error with the source,
than design), so I can remove them.
But eventually, I'd like to find a solution to this.
Thanks Rob
"Michael Rys [MSFT]" wrote:

> Did you add the XML declaration:
> <?xml version="1.0" encoding="iso-8859-1"?>
> in the beginning?
> Best regards
> Michael
> "RobKaratzas" <RobKaratzas@.discussions.microsoft.com> wrote in message
> news:1EF8BE76-F750-47D4-BA3C-6F785744C5B5@.microsoft.com...
>
>
|||I'm sorry, I failed to mention these facts:
1. The validation issues come up in VS.NET 2003
2. I can browse the XMLSchema (http://localhost/XMLSchema.xsd) w/o issue
(with only inserting <?xml version="1.0" encoding="iso-8859-1"?> into the .vsd
Thanks Rob
"Michael Rys [MSFT]" wrote:

> Did you add the XML declaration:
> <?xml version="1.0" encoding="iso-8859-1"?>
> in the beginning?
> Best regards
> Michael
> "RobKaratzas" <RobKaratzas@.discussions.microsoft.com> wrote in message
> news:1EF8BE76-F750-47D4-BA3C-6F785744C5B5@.microsoft.com...
>
>
|||The error message "Restriction of anySimpleType is not
allowed" indicates that the schema contains an error. You should fix the
schema (and use the general xml newsgroup or post the offending type
restriction for more help).
HTH
Michael
"RobKaratzas" <RobKaratzas@.discussions.microsoft.com> wrote in message
news:50F3E074-06A5-4DE8-A56E-36345FD40E0F@.microsoft.com...[vbcol=seagreen]
> Hi Michael
> I added this to the first line (in both the .xsd AND the .XML file). The
> .XML file validates just fine, however, the .xsd (which I've copied
> locally,
> with the DTDs) comes up with:
> C:\Inetpub\wwwroot\XMLSchema.xsd(1359): Restriction of anySimpleType is
> not
> allowed. An error occurred at C:\Inetpub\wwwroot\XMLSchema.xsd, (1359, 4).
> C:\Inetpub\wwwroot\XMLSchema.xsd(1381): Restriction of anySimpleType is
> not
> allowed. An error occurred at C:\Inetpub\wwwroot\XMLSchema.xsd, (1381, 4).
> C:\Inetpub\wwwroot\XMLSchema.xsd(1400): Restriction of anySimpleType is
> not
> allowed. An error occurred at C:\Inetpub\wwwroot\XMLSchema.xsd, (1400, 4).
>
> C:\Inetpub\wwwroot\XMLSchema.xsd(1424): Restriction of anySimpleType is
> not
> allowed. An error occurred at C:\Inetpub\wwwroot\XMLSchema.xsd, (1424, 4).
> (The SimpleType definitions fail to validate.)
> For the time being, there are only several instances in the XML files
> where
> these characters appear (I think this is more of an error with the source,
> than design), so I can remove them.
> But eventually, I'd like to find a solution to this.
> Thanks Rob
> "Michael Rys [MSFT]" wrote: