Binary_Checksum - How secure is it?

Hi,
In one of my client's database some confidential information is stored in an
encrypted format and a different column has the original value in a
Binary_Checksum format.
For example, if 'abc' is a password, its first encrypted and put in Column1.
But a Binary_Checksum of 'abc' is stored in Column2 for comparison purposes.
I am just wondering whether this is secure. Can't the Binary_Checksum value
(26435) be reversed to get the original 'abc'?
Thank you.
Regards,
KarthikHi Karthik,
The hashes generated are only 32 bits long, which is tiny. BOL states that
the probability of a collision is higher than that of the MD5 one-way hash
function, which itself is considered insecure at this point. This means a
hacker has a much greater chance of guessing the password with brute force
or dictionary attacks. Consider the following sample run in SQL 2005:
SELECT BINARY_CHECKSUM('AAAAAAAAAAAAAAAAA')
SELECT BINARY_CHECKSUM('A')
The first thing to notice is the simplicity of the algorithm.
BINARY_CHECKSUM('A') returns 65, which is the ASCII code for.. what else?
'A'. Both of the SELECT statements above produce the same result. Someone
trying to hack this particular system where you use BINARY_CHECKSUM to hash
the password will have a pretty easy time of getting some generated string
to match that hash. I would switch to another hash algorithm like SHA or
something.
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:0AC981BD-2919-496D-9572-D7F5EA38F3A8@.microsoft.com...
> Hi,
> In one of my client's database some confidential information is stored in
> an
> encrypted format and a different column has the original value in a
> Binary_Checksum format.
> For example, if 'abc' is a password, its first encrypted and put in
> Column1.
> But a Binary_Checksum of 'abc' is stored in Column2 for comparison
> purposes.
> I am just wondering whether this is secure. Can't the Binary_Checksum
> value
> (26435) be reversed to get the original 'abc'?
> Thank you.
> Regards,
> Karthik|||Karthik (Karthik@.discussions.microsoft.com) writes:
> In one of my client's database some confidential information is stored
> in an encrypted format and a different column has the original value in
> a Binary_Checksum format.
> For example, if 'abc' is a password, its first encrypted and put in
> Column1. But a Binary_Checksum of 'abc' is stored in Column2 for
> comparison purposes.
> I am just wondering whether this is secure. Can't the Binary_Checksum
> value (26435) be reversed to get the original 'abc'?
I believe the checksum algorithm is not very sophisticated at all, it only
performs some XOR operations. Then again, it's a destroying
transformation. There are many strings that gets the same checksum. So
it's not completely trivial to guess the original text. Unless, of
course, you already have an idea of what it could be.
So it's not certainly not as secure as a real encrypted value.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You need to educate them on the definition of "encrypted" versus "encoded".
It's generally accepted that encrypted means some type of key is required to
decrypt the data, whether it's a certificate or a password. Encoded means no
key is required. You're talking about encoded here.
Ray

> In one of my client's database some confidential information is stored in
> an
> encrypted format|||Any checksum is simply Character by Character XOR. This is the same as for
RAID parity, .zip checksums, etc. It is not intended to be used for
encryption.
It is based on the fact that the XOR operator is commutative and transitive:
A XOR B = C = B XOR A,
B XOR C = A = C XOR B, and
C XOR A = B = A XOR C,
Which is why PARITY works.
Encryption works the same way, which is why you can decrypt. However, to
make it more secure, instead of XORing the characters together, the original
values are XORed with a fixed algorithm hash. The method of constructing
this hash is what determines the strength of the algorithm.
The hash for a checksum is 0, which is extremely simple to hack: I just told
you what it was; no big secret.
Sincerely,
Anthony Thomas
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:0AC981BD-2919-496D-9572-D7F5EA38F3A8@.microsoft.com...
> Hi,
> In one of my client's database some confidential information is stored in
an
> encrypted format and a different column has the original value in a
> Binary_Checksum format.
> For example, if 'abc' is a password, its first encrypted and put in
Column1.
> But a Binary_Checksum of 'abc' is stored in Column2 for comparison
purposes.
> I am just wondering whether this is secure. Can't the Binary_Checksum
value
> (26435) be reversed to get the original 'abc'?
> Thank you.
> Regards,
> Karthik|||Hi Mike, Erland, Ray and Anthony,
Thank you for all the valuable input. I will present these facts to my
client. Hopefully he will agree for a proper hash rather than
binary_checksum()
Thank you!
Regards,
Karthik
"Mike C#" wrote:

> Hi Karthik,
> The hashes generated are only 32 bits long, which is tiny. BOL states tha
t
> the probability of a collision is higher than that of the MD5 one-way hash
> function, which itself is considered insecure at this point. This means a
> hacker has a much greater chance of guessing the password with brute force
> or dictionary attacks. Consider the following sample run in SQL 2005:
> SELECT BINARY_CHECKSUM('AAAAAAAAAAAAAAAAA')
> SELECT BINARY_CHECKSUM('A')
> The first thing to notice is the simplicity of the algorithm.
> BINARY_CHECKSUM('A') returns 65, which is the ASCII code for.. what else?
> 'A'. Both of the SELECT statements above produce the same result. Someon
e
> trying to hack this particular system where you use BINARY_CHECKSUM to has
h
> the password will have a pretty easy time of getting some generated string
> to match that hash. I would switch to another hash algorithm like SHA or
> something.
> "Karthik" <Karthik@.discussions.microsoft.com> wrote in message
> news:0AC981BD-2919-496D-9572-D7F5EA38F3A8@.microsoft.com...
>
>

Best way to secure an exposed SQL Server

Hi experts,
I have an application that my company designed and sold to users years ago
that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
you here!).
There is basically a straight NAT statement from our firewall to the SQL
server that allows ALL inbound traffic to SQL. I am looking for the best
method to secure this connection from the application side (it is under
rewrite). Possibly encode the app to use some sort of VPN or maybe add a
front end server to autheticate the SQL users first, change the port and pass
to the back-end sql server?... I dont know really... can someone please
provide me with some design suggestions to take to my developers in order to
secure and encrypt there SQL sessions for their appications? I am able to
design hardware solutions to assist with this too!
I know its best practices to NOT have SQL open, but this late in the game,
it would take a miracle to get all our customers to change ports. Thanks for
your timely suggestions!!
"Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> Hi experts,
> I have an application that my company designed and sold to users years ago
> that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
> you here!).
There you are :-)

> There is basically a straight NAT statement from our firewall to the SQL
> server that allows ALL inbound traffic to SQL. I am looking for the best
> method to secure this connection from the application side (it is under
> rewrite). Possibly encode the app to use some sort of VPN or maybe add a
> front end server to autheticate the SQL users first, change the port and
> pass
> to the back-end sql server?... I dont know really... can someone
> please
> provide me with some design suggestions to take to my developers in order
> to
> secure and encrypt there SQL sessions for their appications? I am able to
> design hardware solutions to assist with this too!
I wouldnt code that on my own, I would suggest using a software VPN client
which establishs a conection to the internal network and use the SQLServer
the old fashioned way. Exposing the SQLerver is always risky because your
are exposing productional data to the internet and to possible hackers. Even
if you are coding of 99% solution, that would bring nightmares if I would be
responsible for that.
SO my suggestion would be to use a hardware solution on the one sideand a
software / Hardware solution on the other side implementing VPN (perhaps, if
you have money left to implement some securiyt with some kind of external
certification /smartcard solution)

> I know its best practices to NOT have SQL open, but this late in the game,
> it would take a miracle to get all our customers to change ports. Thanks
> for
> your timely suggestions!!
Just my two cents for that.
HTH, Jens Suessmeyer.
|||We use a hardware firewall to only let some specific IP to access the
SQLServer through the Internet, until now, it is fine.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:Olhu%23%230eFHA.1404@.TK2MSFTNGP09.p hx.gbl...
> "Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> There you are :-)
>
> I wouldnt code that on my own, I would suggest using a software VPN
> client which establishs a conection to the internal network and use the
> SQLServer the old fashioned way. Exposing the SQLerver is always risky
> because your are exposing productional data to the internet and to
> possible hackers. Even if you are coding of 99% solution, that would bring
> nightmares if I would be responsible for that.
> SO my suggestion would be to use a hardware solution on the one sideand a
> software / Hardware solution on the other side implementing VPN (perhaps,
> if you have money left to implement some securiyt with some kind of
> external certification /smartcard solution)
>
> Just my two cents for that.
> HTH, Jens Suessmeyer.
>

Best way to secure an exposed SQL Server

Hi experts,
I have an application that my company designed and sold to users years ago
that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
you here!).
There is basically a straight NAT statement from our firewall to the SQL
server that allows ALL inbound traffic to SQL. I am looking for the best
method to secure this connection from the application side (it is under
rewrite). Possibly encode the app to use some sort of VPN or maybe add a
front end server to autheticate the SQL users first, change the port and pas
s
to the back-end sql server?... I dont know really... can someone please
provide me with some design suggestions to take to my developers in order to
secure and encrypt there SQL sessions for their appications' I am able to
design hardware solutions to assist with this too!
I know its best practices to NOT have SQL open, but this late in the game,
it would take a miracle to get all our customers to change ports. Thanks for
your timely suggestions!!"Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> Hi experts,
> I have an application that my company designed and sold to users years ago
> that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
> you here!).
There you are :-)

> There is basically a straight NAT statement from our firewall to the SQL
> server that allows ALL inbound traffic to SQL. I am looking for the best
> method to secure this connection from the application side (it is under
> rewrite). Possibly encode the app to use some sort of VPN or maybe add a
> front end server to autheticate the SQL users first, change the port and
> pass
> to the back-end sql server?... I dont know really... can someone
> please
> provide me with some design suggestions to take to my developers in order
> to
> secure and encrypt there SQL sessions for their appications' I am able to
> design hardware solutions to assist with this too!
I wouldnt code that on my own, I would suggest using a software VPN client
which establishs a conection to the internal network and use the SQLServer
the old fashioned way. Exposing the SQLerver is always risky because your
are exposing productional data to the internet and to possible hackers. Even
if you are coding of 99% solution, that would bring nightmares if I would be
responsible for that.
SO my suggestion would be to use a hardware solution on the one sideand a
software / hardware solution on the other side implementing VPN (perhaps, if
you have money left to implement some securiyt with some kind of external
certification /smartcard solution)

> I know its best practices to NOT have SQL open, but this late in the game,
> it would take a miracle to get all our customers to change ports. Thanks
> for
> your timely suggestions!!
Just my two cents for that.
HTH, Jens Suessmeyer.|||We use a hardware firewall to only let some specific IP to access the
SQLServer through the Internet, until now, it is fine.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:Olhu%23%23
0eFHA.1404@.TK2MSFTNGP09.phx.gbl...
> "Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> There you are :-)
>
> I wouldnt code that on my own, I would suggest using a software VPN
> client which establishs a conection to the internal network and use the
> SQLServer the old fashioned way. Exposing the SQLerver is always risky
> because your are exposing productional data to the internet and to
> possible hackers. Even if you are coding of 99% solution, that would bring
> nightmares if I would be responsible for that.
> SO my suggestion would be to use a hardware solution on the one sideand a
> software / hardware solution on the other side implementing VPN (perhaps,
> if you have money left to implement some securiyt with some kind of
> external certification /smartcard solution)
>
> Just my two cents for that.
> HTH, Jens Suessmeyer.
>

Best way to secure an exposed SQL Server

Hi experts,
I have an application that my company designed and sold to users years ago
that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
you here!).
There is basically a straight NAT statement from our firewall to the SQL
server that allows ALL inbound traffic to SQL. I am looking for the best
method to secure this connection from the application side (it is under
rewrite). Possibly encode the app to use some sort of VPN or maybe add a
front end server to autheticate the SQL users first, change the port and pass
to the back-end sql server?... I dont know really... can someone please
provide me with some design suggestions to take to my developers in order to
secure and encrypt there SQL sessions for their appications' I am able to
design hardware solutions to assist with this too!
I know its best practices to NOT have SQL open, but this late in the game,
it would take a miracle to get all our customers to change ports. Thanks for
your timely suggestions!!"Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> Hi experts,
> I have an application that my company designed and sold to users years ago
> that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
> you here!).
There you are :-)
> There is basically a straight NAT statement from our firewall to the SQL
> server that allows ALL inbound traffic to SQL. I am looking for the best
> method to secure this connection from the application side (it is under
> rewrite). Possibly encode the app to use some sort of VPN or maybe add a
> front end server to autheticate the SQL users first, change the port and
> pass
> to the back-end sql server?... I dont know really... can someone
> please
> provide me with some design suggestions to take to my developers in order
> to
> secure and encrypt there SQL sessions for their appications' I am able to
> design hardware solutions to assist with this too!
I wouldn´t code that on my own, I would suggest using a software VPN client
which establishs a conection to the internal network and use the SQLServer
the old fashioned way. Exposing the SQLerver is always risky because your
are exposing productional data to the internet and to possible hackers. Even
if you are coding of 99% solution, that would bring nightmares if I would be
responsible for that.
SO my suggestion would be to use a hardware solution on the one sideand a
software / Hardware solution on the other side implementing VPN (perhaps, if
you have money left to implement some securiyt with some kind of external
certification /smartcard solution)
> I know its best practices to NOT have SQL open, but this late in the game,
> it would take a miracle to get all our customers to change ports. Thanks
> for
> your timely suggestions!!
Just my two cents for that.
HTH, Jens Suessmeyer.|||We use a hardware firewall to only let some specific IP to access the
SQLServer through the Internet, until now, it is fine.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> ¼¶¼g©ó¶l¥ó·s»D:Olhu%23%230eFHA.1404@.TK2MSFTNGP09.phx.gbl...
> "Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
>> Hi experts,
>> I have an application that my company designed and sold to users years
>> ago
>> that requires SQL port 1433 to be open to the Internet. (Insert .. shame
>> a
>> you here!).
> There you are :-)
>> There is basically a straight NAT statement from our firewall to the SQL
>> server that allows ALL inbound traffic to SQL. I am looking for the best
>> method to secure this connection from the application side (it is under
>> rewrite). Possibly encode the app to use some sort of VPN or maybe add a
>> front end server to autheticate the SQL users first, change the port and
>> pass
>> to the back-end sql server?... I dont know really... can someone
>> please
>> provide me with some design suggestions to take to my developers in order
>> to
>> secure and encrypt there SQL sessions for their appications' I am able
>> to
>> design hardware solutions to assist with this too!
> I wouldn´t code that on my own, I would suggest using a software VPN
> client which establishs a conection to the internal network and use the
> SQLServer the old fashioned way. Exposing the SQLerver is always risky
> because your are exposing productional data to the internet and to
> possible hackers. Even if you are coding of 99% solution, that would bring
> nightmares if I would be responsible for that.
> SO my suggestion would be to use a hardware solution on the one sideand a
> software / Hardware solution on the other side implementing VPN (perhaps,
> if you have money left to implement some securiyt with some kind of
> external certification /smartcard solution)
>> I know its best practices to NOT have SQL open, but this late in the
>> game,
>> it would take a miracle to get all our customers to change ports. Thanks
>> for
>> your timely suggestions!!
> Just my two cents for that.
> HTH, Jens Suessmeyer.
>