Showing posts with label program. Show all posts
Showing posts with label program. Show all posts

Thursday, March 29, 2012

EndDialog

I have a SB program that sends a message and there is an activation procedure on my queue. The activation procedure takes the message from the queue, does some processing with the data, and ends the conversation. (I may, in the future, consider reusing the dialogs.) What I am wondering about is that I never come across the

'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' message type name that indicates that the dialog has ended. Any ideas why?

From your description it sounds like the activated procedure issues the END CONVERSATION verb after processing the first message. After the END CONVERSATION verb was issued on a handle, you will not receive any other message on that conversation (including EndDialog sent by the peer). If we wouldn't ensure that application would have to keep around the state related to that conversation for an undetermined time.

HTH,

~ Remus

P.S. Thanks for writing those SSB articles

|||

Thanks Remus, that definetly makes sense. Because I won't see that EndDialog message type, what is the best way to go about cleaning the conversation up once I am finished? What I have ran into so far is that when I used END CONVERSATION WITH CLEANUP, the conversation remains in sys.conversation_endpoints with a status of 'Conversing', but if I omit 'WITH CLEANUP', the dialog will close as expected. I would like a way to cleanup that message so that it is removed from the catalog view. Does that make sense?

Thanks again,

Tim

|||

Never ever use END CONVERSATION ... WITH CLEANUP. It is realy a last resort statement itended for administrators. Using it in applications can result in very serious problems.

You must decide who ends the conversation first, depending on the business semantics of the conversation. The party that ends the conversation first is the first one that can say 'I am no longer interested in this conversation, even if this last (EndDialog) message never makes it to my peer'. Some common patterns are:

Notification

1) Initiator begins conversation

2) Initiator sends the notification message

3) Target receives the message

4) Target ends the conversations

5) Initiator receives EndDialog message

6) Initiator ends the conversation

Request-Response (when target does not care is response is lost)

1) Initiator begins conversation

2) Initiator sends the request message

3) Target receives the message

4) Target sends response

5) Target ends the conversations

6) Intiator receives the response

7) Initiator receives EndDialog message

8) Initiator ends the conversation

Request-Response (when target does care is response is lost)

1) Initiator begins conversation

2) Initiator sends the request message

3) Target receives the message

4) Target sends response

5) Intiator receives the response

6) Initiator ends the conversation

7) target receives EndDialog message

8) target ends the conversation

One way stream of messages

1) Initiator begins conversation

2) Initiator sends the one message

2') Initiator sends the one message ...

3) Target receives message(s)

... conversation continues for while with initiator sending messages

4) Initiator decides to intrerupt the stream and sends a special application message (EndOfStream)

5) Target receives the EndOfSream message

6) Target ends the conversations

7) Initiator receives EndDialog message

8) Initiator ends the conversation

One pattern that is actually incorrect is fire-and-forget: initiator begins a conversation, then sends one or more messages, then ends the conversation. This way the initiator never gets any feedback if the messages we actually sent or not. I have a blog entry on this subject: http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx

It is important to mention the conversation lifetime role in these patterns. If a conversation is not ended by both sides before it's lifetime expires, the conversation is errored and an Error message is sent to any endpoint still open (not ended). This is why endpoints that still care about delivery of the last message sent cannot issue an END CONVERSATION. They are supposed to send the message and the if they get an EndDialog message is a confrmation of succesfull delivery, while an Error message is an indication of a problem.

As about the endpoint states, a 'Conversing' endpoint is an endpoint that did not receive nor sent an EndDialog message. All of the patterns I mention above clean up the endpoints after issuing the END CONVERSATION. The first endpoint that issues the END CONVERSATION will be kept in the system until the EndDialog message is acknowledged by the peer (in an DISCONNECTED_OUTBOUND state), then it will be closed. An endpoint that has received an EndDialog message will stay in DISCONNECTED_INBOUND state until is explictly ended with END CONVERSATION, then it will be closed.

When initiator endpoint is closed, the endpoint is immedeately deleted.

When target endpoint is closed, the endpoint might be kept around for up to 30 minutes to prevent a replay attack, then it will be deleted.

Another negative side effect of the fire-and-forget pattern is that the target endpoint is not kept for 30 minutes in that case, but until the original conversation lifetime expires. Since most application do not specify a lifetime, that mens the target is scheduled to be deleted 74 years from now, thus being leaked for all practical means.

HTH,

~ Remus

|||Thanks again Remus, this is perfect. I was confused about the dialogs getting cleaned up. I noticed that the WITH CLEANUP cleaned the dialog up, and that is what I wanted at the time. I have since taken this out of all of my code. I didn't realize that the dialogs got cleaned up after 30 minutes to prevent a replay attack. Thanks again for your help...I love this new feature of 2005.
Timsql

Monday, March 26, 2012

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

Monday, March 19, 2012

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.

encrypting a column in SQL 7

Does anybody know a reliable program that I can use to encrypt confidential
data in SQL 7? I just want to encrypt a column in a table and as it's going
to be implemented on an existing database, I 'm looking for something that
won't change the queries or the programming. Any ideas'
Thanks in advance.Take a look at:
http://www.activecrypt.com/faq.html
--
-oj
http://www.rac4sql.net
"Fulya Erol" <ferol@.no.nospam.mezun.com> wrote in message
news:%23b2HCgu3DHA.1096@.TK2MSFTNGP11.phx.gbl...
> Does anybody know a reliable program that I can use to encrypt
confidential
> data in SQL 7? I just want to encrypt a column in a table and as it's
going
> to be implemented on an existing database, I 'm looking for something that
> won't change the queries or the programming. Any ideas'
> Thanks in advance.
>

encrypting a column in SQL 7

Does anybody know a reliable program that I can use to encrypt confidential
data in SQL 7? I just want to encrypt a column in a table and as it's going
to be implemented on an existing database, I 'm looking for something that
won't change the queries or the programming. Any ideas'
Thanks in advance.Take a look at:
http://www.activecrypt.com/faq.html
-oj
http://www.rac4sql.net
"Fulya Erol" <ferol@.no.nospam.mezun.com> wrote in message
news:%23b2HCgu3DHA.1096@.TK2MSFTNGP11.phx.gbl...
quote:

> Does anybody know a reliable program that I can use to encrypt

confidential
quote:

> data in SQL 7? I just want to encrypt a column in a table and as it's

going
quote:

> to be implemented on an existing database, I 'm looking for something that
> won't change the queries or the programming. Any ideas'
> Thanks in advance.
>

Friday, March 9, 2012

Encrpyt

Hi
What is the best method to encrypt data in tables?
Lets say if special program or user calls select * from names
then all data is visible and normal, but all athers get "crap" with select *
from names
;)
Best Regards;
Mex
Hello,
Take a look into Symmetric or Asymmetric encryptions in SQL 2005.The below
URL details various encryption methods and algorithems
which is available in SQL Server.
http://www.microsoft.com/technet/itshowcase/content/sqldatsec.mspx
Thanks
Hari
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uNYS7XSdHHA.4344@.TK2MSFTNGP02.phx.gbl...
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select
> * from names
>
> ;)
> Best Regards;
> Mex
>
|||let me explain what i want
this "encrpyt" function is needed for web application.
lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
and hackers can use sql injection attack
to get data from database.
when data is encrypted even with sqlinjection hackers can't get sensitive
data.
sry my english is not very good
Mex
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1175523112.383268.101970@.b75g2000hsg.googlegr oups.com...
> On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
> Do you really want to return "crap" to some users? I suspect not. I
> think your real requirement is to deny user access to data certain
> data. You can do that using GRANT / DENY and no encryption is
> required.
> Encryption is not a substitute for data access control.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||Hi
"Redivivus" wrote:

> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
> sry my english is not very good
> Mex
>
Hari has suggested using SQL 2005 encryption features, but you have not said
if you are using SQL 2005!
If you have left yourself vulnerable to SQL Injection attacks then the
hacker may be able to find out what encryption keys are being used or in a
worse case if the victim procedure has opened the keys he may still be able
to see the data through the injection anyhow. This could apply to any method
of server based encryption, you may want to read
http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx
Part of your solution should be to make sure that all code is reviewed and a
coding standards implemented and maybe justification should be required when
it is necessary to use dynamic SQL. You may want to read the following
http://www.sommarskog.se/dynamic_sql.html#good_practices
HTH
John
|||On 2 Apr, 17:01, "Redivivus" <meelis.lil...@.deltmar.ee> wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
>
You are mistaken. If you allow arbitrary code execution through SQL
injection then anything could be possible within the security context
of the connection. If the authentication layer for your encryption
(password, certificate or some other method) is compromised as a
result then your encryption is worthless. Encryption is no substitute
for controlling data access.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||"Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
news:ew3rnAUdHHA.1080@.TK2MSFTNGP02.phx.gbl...
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad
> code and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
Sure they can. Because if you have programmers that bad, you'll also do
encryption that poorly.
Ok, granted, I do agree that some encryption is a good idea, but I'd spend
more time with better hiring. :-)
Note, regardless, you still do NOT want to encrypt every column. You really
can't get performance that way. (as in you'll get NONE).

> sry my english is not very good
> Mex
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1175523112.383268.101970@.b75g2000hsg.googlegr oups.com...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Encrpyt

Hi
What is the best method to encrypt data in tables?
Lets say if special program or user calls select * from names
then all data is visible and normal, but all athers get "crap" with select *
from names
;)
Best Regards;
MexHello,
Take a look into Symmetric or Asymmetric encryptions in SQL 2005.The below
URL details various encryption methods and algorithems
which is available in SQL Server.
http://www.microsoft.com/technet/it.../sqldatsec.mspx
Thanks
Hari
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uNYS7XSdHHA.4344@.TK2MSFTNGP02.phx.gbl...
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select
> * from names
>
> ;)
> Best Regards;
> Mex
>|||On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select
*
> from names
>
Do you really want to return "crap" to some users? I suspect not. I
think your real requirement is to deny user access to data certain
data. You can do that using GRANT / DENY and no encryption is
required.
Encryption is not a substitute for data access control.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||let me explain what i want
this "encrpyt" function is needed for web application.
lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
and hackers can use sql injection attack
to get data from database.
when data is encrypted even with sqlinjection hackers can't get sensitive
data.
sry my english is not very good
Mex
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1175523112.383268.101970@.b75g2000hsg.googlegroups.com...
> On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
> Do you really want to return "crap" to some users? I suspect not. I
> think your real requirement is to deny user access to data certain
> data. You can do that using GRANT / DENY and no encryption is
> required.
> Encryption is not a substitute for data access control.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Hi
"Redivivus" wrote:

> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad co
de
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
> sry my english is not very good
> Mex
>
Hari has suggested using SQL 2005 encryption features, but you have not said
if you are using SQL 2005!
If you have left yourself vulnerable to SQL Injection attacks then the
hacker may be able to find out what encryption keys are being used or in a
worse case if the victim procedure has opened the keys he may still be able
to see the data through the injection anyhow. This could apply to any method
of server based encryption, you may want to read
http://blogs.msdn.com/lcris/archive...encryption.aspx
Part of your solution should be to make sure that all code is reviewed and a
coding standards implemented and maybe justification should be required when
it is necessary to use dynamic SQL. You may want to read the following
http://www.sommarskog.se/dynamic_sq...#good_practices
HTH
John|||On 2 Apr, 17:01, "Redivivus" <meelis.lil...@.deltmar.ee> wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad co
de
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
>
You are mistaken. If you allow arbitrary code execution through SQL
injection then anything could be possible within the security context
of the connection. If the authentication layer for your encryption
(password, certificate or some other method) is compromised as a
result then your encryption is worthless. Encryption is no substitute
for controlling data access.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
news:ew3rnAUdHHA.1080@.TK2MSFTNGP02.phx.gbl...
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad
> code and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
Sure they can. Because if you have programmers that bad, you'll also do
encryption that poorly.
Ok, granted, I do agree that some encryption is a good idea, but I'd spend
more time with better hiring. :-)
Note, regardless, you still do NOT want to encrypt every column. You really
can't get performance that way. (as in you'll get NONE).

> sry my english is not very good
> Mex
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1175523112.383268.101970@.b75g2000hsg.googlegroups.com...
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Encrpyt

Hi
What is the best method to encrypt data in tables?
Lets say if special program or user calls select * from names
then all data is visible and normal, but all athers get "crap" with select *
from names
;)
Best Regards;
MexHello,
Take a look into Symmetric or Asymmetric encryptions in SQL 2005.The below
URL details various encryption methods and algorithems
which is available in SQL Server.
http://www.microsoft.com/technet/itshowcase/content/sqldatsec.mspx
Thanks
Hari
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uNYS7XSdHHA.4344@.TK2MSFTNGP02.phx.gbl...
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select
> * from names
>
> ;)
> Best Regards;
> Mex
>|||On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select *
> from names
>
Do you really want to return "crap" to some users? I suspect not. I
think your real requirement is to deny user access to data certain
data. You can do that using GRANT / DENY and no encryption is
required.
Encryption is not a substitute for data access control.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||let me explain what i want
this "encrpyt" function is needed for web application.
lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
and hackers can use sql injection attack
to get data from database.
when data is encrypted even with sqlinjection hackers can't get sensitive
data.
sry my english is not very good :)
Mex
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1175523112.383268.101970@.b75g2000hsg.googlegroups.com...
> On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
>> Hi
>> What is the best method to encrypt data in tables?
>> Lets say if special program or user calls select * from names
>> then all data is visible and normal, but all athers get "crap" with
>> select *
>> from names
> Do you really want to return "crap" to some users? I suspect not. I
> think your real requirement is to deny user access to data certain
> data. You can do that using GRANT / DENY and no encryption is
> required.
> Encryption is not a substitute for data access control.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Hi
"Redivivus" wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
> sry my english is not very good :)
> Mex
>
Hari has suggested using SQL 2005 encryption features, but you have not said
if you are using SQL 2005!
If you have left yourself vulnerable to SQL Injection attacks then the
hacker may be able to find out what encryption keys are being used or in a
worse case if the victim procedure has opened the keys he may still be able
to see the data through the injection anyhow. This could apply to any method
of server based encryption, you may want to read
http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx
Part of your solution should be to make sure that all code is reviewed and a
coding standards implemented and maybe justification should be required when
it is necessary to use dynamic SQL. You may want to read the following
http://www.sommarskog.se/dynamic_sql.html#good_practices
HTH
John|||On 2 Apr, 17:01, "Redivivus" <meelis.lil...@.deltmar.ee> wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
>
You are mistaken. If you allow arbitrary code execution through SQL
injection then anything could be possible within the security context
of the connection. If the authentication layer for your encryption
(password, certificate or some other method) is compromised as a
result then your encryption is worthless. Encryption is no substitute
for controlling data access.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
news:ew3rnAUdHHA.1080@.TK2MSFTNGP02.phx.gbl...
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad
> code and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
Sure they can. Because if you have programmers that bad, you'll also do
encryption that poorly.
Ok, granted, I do agree that some encryption is a good idea, but I'd spend
more time with better hiring. :-)
Note, regardless, you still do NOT want to encrypt every column. You really
can't get performance that way. (as in you'll get NONE).
> sry my english is not very good :)
> Mex
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1175523112.383268.101970@.b75g2000hsg.googlegroups.com...
>> On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
>> Hi
>> What is the best method to encrypt data in tables?
>> Lets say if special program or user calls select * from names
>> then all data is visible and normal, but all athers get "crap" with
>> select *
>> from names
>>
>> Do you really want to return "crap" to some users? I suspect not. I
>> think your real requirement is to deny user access to data certain
>> data. You can do that using GRANT / DENY and no encryption is
>> required.
>> Encryption is not a substitute for data access control.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Wednesday, March 7, 2012

Encripting a field in a table

I have inherited a database program that has a table for usernames and passwords. Unfortunetly, I must give certain users read only access to the SQL tables. Therefore, the Usernames and the passwords can be viewed. Question isw there a way of encripting this field so that it can't be read?

Thanks in advance!!!not sure i understand the question? You need to give users access to read the data in the table, but you want the data encrypted so it can't be read?|||The SQL DB is the backend and the Front end is a VBA program. I have to give certian users direct access to the tables (backend). I want to still be able to use the user/password table when users access the front end but I want to prevent users from viewing the passwords while they are accessing the tables directly from the SQL DB.|||Hi,

you can use pwdencrypt sql function., but there are certain thing u have to keep in mind..

The field should be nvarchar.
u cannt unencrypt the string after encrypting it.

select pwdencrypt('abc')

this is how u can compare 2 strings -
select pwdcompare('abc',pwdencrypt('abc'))
1 for true
0 for false.

hope this will help u.
Cheers
Gola munjal

Originally posted by Chumpie999typla
I have inherited a database program that has a table for usernames and passwords. Unfortunetly, I must give certain users read only access to the SQL tables. Therefore, the Usernames and the passwords can be viewed. Question isw there a way of encripting this field so that it can't be read?

Thanks in advance!!!

Wednesday, February 15, 2012

Empty contents of database

Hi All,
We have an SQL database that is filled by a Cisco call accounting program.
This program can only put data in a database which is less then 1.5GB in
size. Currently the database is 1.5GB and so the program can no longer keep
data.
What we wish to do is get rid of all records from this database, but leave
the table structure, etc.. in tact. We did try using just a blank database,
but the program does not work in that situation..
To that end, how can we empty the SQL database of records without upsetting
any of the other database properties.
ThanksHi
This sounds like you may be using MSDE which has a 2GB limit. You could
upgrade to a version of SQL Server that does not have this restriction!
If you do need to delete the data, make sure that you don't delete
static/configuration data that is required by the application to function
correctly, which could be why the empty database did not work (assuming you
did have tables in there!).
If you are deleting data from a table you will need to delete data from any
table that references it with a foreign key unless the FK has cascading
deletes, or alternatively you can disable foreign keys before deleting the
data and then re-instating them.
This may help you find which tables may be used most and therefore not
static data!
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
How to find foreign keys:
http://www.aspfaq.com/show.asp?id=2520
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
If you are deleting a large amount of data you may want to set the recovery
mode to simple during the excercise. You may want to TRUNCATE the table
rather than deleting rows. Truncation will reset identity values, you may
have to reset the identity values separately if you use delete.
Once you have a working template database, you can back it up and then you
will only need to restore the template database instead of deleting data the
next time it gets full.
John
"Luke Cassar" wrote:
> Hi All,
> We have an SQL database that is filled by a Cisco call accounting program.
> This program can only put data in a database which is less then 1.5GB in
> size. Currently the database is 1.5GB and so the program can no longer keep
> data.
> What we wish to do is get rid of all records from this database, but leave
> the table structure, etc.. in tact. We did try using just a blank database,
> but the program does not work in that situation..
> To that end, how can we empty the SQL database of records without upsetting
> any of the other database properties.
> Thanks

Empty contents of database

Hi All,
We have an SQL database that is filled by a Cisco call accounting program.
This program can only put data in a database which is less then 1.5GB in
size. Currently the database is 1.5GB and so the program can no longer keep
data.
What we wish to do is get rid of all records from this database, but leave
the table structure, etc.. in tact. We did try using just a blank database,
but the program does not work in that situation..
To that end, how can we empty the SQL database of records without upsetting
any of the other database properties.
Thanks
Hi
This sounds like you may be using MSDE which has a 2GB limit. You could
upgrade to a version of SQL Server that does not have this restriction!
If you do need to delete the data, make sure that you don't delete
static/configuration data that is required by the application to function
correctly, which could be why the empty database did not work (assuming you
did have tables in there!).
If you are deleting data from a table you will need to delete data from any
table that references it with a foreign key unless the FK has cascading
deletes, or alternatively you can disable foreign keys before deleting the
data and then re-instating them.
This may help you find which tables may be used most and therefore not
static data!
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
How to find foreign keys:
http://www.aspfaq.com/show.asp?id=2520
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
If you are deleting a large amount of data you may want to set the recovery
mode to simple during the excercise. You may want to TRUNCATE the table
rather than deleting rows. Truncation will reset identity values, you may
have to reset the identity values separately if you use delete.
Once you have a working template database, you can back it up and then you
will only need to restore the template database instead of deleting data the
next time it gets full.
John
"Luke Cassar" wrote:

> Hi All,
> We have an SQL database that is filled by a Cisco call accounting program.
> This program can only put data in a database which is less then 1.5GB in
> size. Currently the database is 1.5GB and so the program can no longer keep
> data.
> What we wish to do is get rid of all records from this database, but leave
> the table structure, etc.. in tact. We did try using just a blank database,
> but the program does not work in that situation..
> To that end, how can we empty the SQL database of records without upsetting
> any of the other database properties.
> Thanks

Empty contents of database

Hi All,
We have an SQL database that is filled by a cisco call accounting program.
This program can only put data in a database which is less then 1.5GB in
size. Currently the database is 1.5GB and so the program can no longer keep
data.
What we wish to do is get rid of all records from this database, but leave
the table structure, etc.. in tact. We did try using just a blank database,
but the program does not work in that situation..
To that end, how can we empty the SQL database of records without upsetting
any of the other database properties.
ThanksHi
This sounds like you may be using MSDE which has a 2GB limit. You could
upgrade to a version of SQL Server that does not have this restriction!
If you do need to delete the data, make sure that you don't delete
static/configuration data that is required by the application to function
correctly, which could be why the empty database did not work (assuming you
did have tables in there!).
If you are deleting data from a table you will need to delete data from any
table that references it with a foreign key unless the FK has cascading
deletes, or alternatively you can disable foreign keys before deleting the
data and then re-instating them.
This may help you find which tables may be used most and therefore not
static data!
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
How to find foreign keys:
http://www.aspfaq.com/show.asp?id=2520
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
If you are deleting a large amount of data you may want to set the recovery
mode to simple during the excercise. You may want to TRUNCATE the table
rather than deleting rows. Truncation will reset identity values, you may
have to reset the identity values separately if you use delete.
Once you have a working template database, you can back it up and then you
will only need to restore the template database instead of deleting data the
next time it gets full.
John
"Luke Cassar" wrote:

> Hi All,
> We have an SQL database that is filled by a cisco call accounting program.
> This program can only put data in a database which is less then 1.5GB in
> size. Currently the database is 1.5GB and so the program can no longer ke
ep
> data.
> What we wish to do is get rid of all records from this database, but leave
> the table structure, etc.. in tact. We did try using just a blank database
,
> but the program does not work in that situation..
> To that end, how can we empty the SQL database of records without upsettin
g
> any of the other database properties.
> Thanks