The "Best Practice" for securing my vb.net/SQL connection.

[=?Utf-8?B?QW1tZXI=?=]

I've read many incomplete opinions about the "Best Practice" for securely
accessing SQL but what I really need to find the "Best Practice" that fits my
applications needs.

Currently (alpha stage) I am Using a .Net DSN-Less SQLConnection method in
my client program (vb.net) and sending uid/pwd across the network. The
client only
calls upon the stored procedures to access the tables in SQL 2005.

This is a semi commercial application which will be installed by 3rd party
on an active directory networks. The database will house mostly encrypted
information so I want to keep people, from messing with it.

Any ideas on how I should tackle this?

------------------------------------------------------------------------

I've been reading alot and not liking what I'm seeing so far.

I can tell right away that I'm going to have to use a trusted connection and
add users to the database. Which in return allows them to logon to the server
(GRRR) using the management studio. Doing that means they can play with the
SP's, using them to not only read but write to all that the SP has access to.
Plus they can see other users and look at any account I might be using to
"EXECUTE AS" and use that to their advantage.

There are only two ways that I can figure out to secure my DB from tampering.
First way:
1. Create a master SP that has the rights to execute the existing app SPs.
2. Give my users access to just the master SP.
3. In my program instead of executing the SPs directly i would have to
encrypt the target sp and params and pass them to the master SP.
4. The master SP would then decrypt and forward on to the right app SP.

Second Way:
1. Implement a required parameter in each SP that can be used to verified
that my application sent the request and not someone jacking with a query
analyzer.

I'm not looking to stop the elite hacker here but I am trying to keep my
customers information safe from the SQL savvy users who are not DBA's.

[Tom Shelton]

On Nov 7, 5:31 pm, Ammer <Am...@discussi ons.microsoft.c omwrote:

I've read many incomplete opinions about the "Best Practice" for securely
accessing SQL but what I really need to find the "Best Practice" that fits my
applications needs.

Currently (alpha stage) I am Using a .Net DSN-Less SQLConnection method in
my client program (vb.net) and sending uid/pwd across the network. The
client only
calls upon the stored procedures to access the tables in SQL 2005.

This is a semi commercial application which will be installed by 3rd party
on an active directory networks. The database will house mostly encrypted
information so I want to keep people, from messing with it.

Any ideas on how I should tackle this?

------------------------------------------------------------------------

I've been reading alot and not liking what I'm seeing so far.

I can tell right away that I'm going to have to use a trusted connection and
add users to the database. Which in return allows them to logon to the server
(GRRR) using the management studio. Doing that means they can play with the
SP's, using them to not only read but write to all that the SP has access to.
Plus they can see other users and look at any account I might be using to
"EXECUTE AS" and use that to their advantage.

There are only two ways that I can figure out to secure my DB from tampering.
First way:
1. Create a master SP that has the rights to execute the existing app SPs.
2. Give my users access to just the master SP.
3. In my program instead of executing the SPs directly i would have to
encrypt the target sp and params and pass them to the master SP.
4. The master SP would then decrypt and forward on to the right app SP.

Second Way:
1. Implement a required parameter in each SP that can be used to verified
that my application sent the request and not someone jacking with a query
analyzer.

I'm not looking to stop the elite hacker here but I am trying to keep my
customers information safe from the SQL savvy users who are not DBA's.

Adding user does not mean that they have to have full access. You can
grant the user's access to just the schema's that you want, as well as
limit what they can do with your database objects.

--
Tom Shelton

[=?Utf-8?B?QW1tZXI=?=]

Adding user does not mean that they have to have full access. You can

grant the user's access to just the schema's that you want, as well as
limit what they can do with your database objects.

I understand that but if I lock all users down to the SP execute they can
still login tamper with the SP in SQL Manager and see the other accounts i
use to "Execute As"

[Cor Ligthert[MVP]]

Ammer,

There is no "Best Practice" in .Net, you can use *your* Best Practice based
on the problems that you have.

If there is "Best Practise", then there is no alternative.

Cor

[RB]

Ammer wrote:

>Adding user does not mean that they have to have full access. You can
grant the user's access to just the schema's that you want, as well as
limit what they can do with your database objects.

I understand that but if I lock all users down to the SP execute they can
still login tamper with the SP in SQL Manager and see the other accounts i
use to "Execute As"

What do you mean "login tamper" with an SP?

If you only give users Execute permissions on stored procedures and no
other permissions, I don't really see what they can do to break things.
Yes, they may be able to see other accounts, but I assume they won't
have passwords to those accounts, so I think it's a moot point.

From Books Online:
"ALTER PROCEDURE permissions default to members of the sysadmin fixed
server role, and the db_owner and db_ddladmin fixed database roles, and
the owner of the procedure, and are not transferable."

So they will not be able to alter your stored procedures...

Not sure if any of that helps - I feel I've misunderstood your question,
but I'm not sure where!!

Cheers,

RB.

[=?Utf-8?B?QW1tZXI=?=]

Very helpfull thanks. 8)

"Cor Ligthert[MVP]" wrote:

Ammer,

There is no "Best Practice" in .Net, you can use *your* Best Practice based
on the problems that you have.

If there is "Best Practise", then there is no alternative.

Cor

[=?Utf-8?B?QW1tZXI=?=]

By tamper I mean. Bypass the security built into the end client by using the
SP to run select and update statments at will. I don't think its a moot point
though. Its the data people want to protect not so much the passwords.

What do you mean "login tamper" with an SP?

If you only give users Execute permissions on stored procedures and no
other permissions, I don't really see what they can do to break things.
Yes, they may be able to see other accounts, but I assume they won't
have passwords to those accounts, so I think it's a moot point.

From Books Online:
"ALTER PROCEDURE permissions default to members of the sysadmin fixed
server role, and the db_owner and db_ddladmin fixed database roles, and
the owner of the procedure, and are not transferable."

So they will not be able to alter your stored procedures...

Not sure if any of that helps - I feel I've misunderstood your question,
but I'm not sure where!!

Cheers,

RB.

[sloan]

This is a repeat, but I'll add some extra info:

What I suggest is:

Use Sql Authentication
Create a login/db user.
Give this user the most anal, most restrictive permissions possible. Aka,
only what they bare bones need.
And encrypt the .config file.

Using the enterpriselibra ry.data is suggested:
http://msdn.microsoft.com/msdnmag/is...07/DataPoints/
because they have "how to encrypt" done for you very nicely.

............

If they don't have nt_autheniticat ion, then they can't do anything based on
their nt credentials. (Which is your primary concern)
If you encrypt the config files, they can't use sql authentication
credentials to get in, because they don't know them.

...

Good luck.

"Ammer" <Am***@discussi ons.microsoft.c omwrote in message
news:E0******** *************** ***********@mic rosoft.com...

I've read many incomplete opinions about the "Best Practice" for securely
accessing SQL but what I really need to find the "Best Practice" that fits
my
applications needs.

Currently (alpha stage) I am Using a .Net DSN-Less SQLConnection method in
my client program (vb.net) and sending uid/pwd across the network. The
client only
calls upon the stored procedures to access the tables in SQL 2005.

This is a semi commercial application which will be installed by 3rd party
on an active directory networks. The database will house mostly encrypted
information so I want to keep people, from messing with it.

Any ideas on how I should tackle this?

------------------------------------------------------------------------

I've been reading alot and not liking what I'm seeing so far.

I can tell right away that I'm going to have to use a trusted connection
and
add users to the database. Which in return allows them to logon to the
server
(GRRR) using the management studio. Doing that means they can play with
the
SP's, using them to not only read but write to all that the SP has access
to.
Plus they can see other users and look at any account I might be using to
"EXECUTE AS" and use that to their advantage.

There are only two ways that I can figure out to secure my DB from
tampering.
First way:
1. Create a master SP that has the rights to execute the existing app SPs.
2. Give my users access to just the master SP.
3. In my program instead of executing the SPs directly i would have to
encrypt the target sp and params and pass them to the master SP.
4. The master SP would then decrypt and forward on to the right app SP.

Second Way:
1. Implement a required parameter in each SP that can be used to verified
that my application sent the request and not someone jacking with a query
analyzer.

I'm not looking to stop the elite hacker here but I am trying to keep my
customers information safe from the SQL savvy users who are not DBA's.

[sloan]

He is saying that there is not 1 (singular) "best" practice.

There are several options, and you gotta pick based on your needs.

Which he is correct.

The best way to state the problem is:

"I'm looking for the best solution that meets these particuliar needs". "Do
you have any suggestions?"

.............


"Ammer" <Am***@discussi ons.microsoft.c omwrote in message
news:74******** *************** ***********@mic rosoft.com...

Very helpfull thanks. 8)

"Cor Ligthert[MVP]" wrote:

>Ammer,

There is no "Best Practice" in .Net, you can use *your* Best Practice
based
on the problems that you have.

If there is "Best Practise", then there is no alternative.

Cor

[RB]

Hi Ammer,

Okay, I think my original understanding was basically correct.

Users will not be able to bypass the security built into the end client
by using the SP to run select and update statements at will, because
they cannot alter the stored procedures (as you would not have added
them to a group which can alter procedures).

They will be able to execute the existing SPs however, thus bypassing
any front-end validation your client application does. If this is your
concern then I guess you'll have to employ one of the methods you
outlined earlier.

Sorry that's not much help!!

Cheers,

RB.

Ammer wrote:

By tamper I mean. Bypass the security built into the end client by using the
SP to run select and update statments at will. I don't think its a moot point
though. Its the data people want to protect not so much the passwords.

>What do you mean "login tamper" with an SP?

If you only give users Execute permissions on stored procedures and no
other permissions, I don't really see what they can do to break things.
Yes, they may be able to see other accounts, but I assume they won't
have passwords to those accounts, so I think it's a moot point.

From Books Online:
"ALTER PROCEDURE permissions default to members of the sysadmin fixed
server role, and the db_owner and db_ddladmin fixed database roles, and
the owner of the procedure, and are not transferable."

So they will not be able to alter your stored procedures...

Not sure if any of that helps - I feel I've misunderstood your question,
but I'm not sure where!!

Cheers,

RB.