Dear all,

I'm designing a system including the database and the security
represents the most crucial aspect of the system; hence for the
database security i have implemented the following aspects and need
your advise on further aspects or perhaps corrections where by the
system is web based using asp.net and under iis 6.0 with https; in the
asp.net engine side, i have included client-side validations for what
ever is inputed and validated against sql injections for postback
forms:
The features of security in SQL Server 2005 side i have implemented:
1.) Created MACHINE\ASPNET Account
2.) Allowed ASPNET Account to access the DB
3.) Explicity denied ASPNET Account all permissions to all tables,
functions and views
4.) Denied all permissions to the ASPNET user for stored procedures
except EXECUTE permissions
5.) By Stored procedure creation, WITH ENCRYPTION, EXECUTE AS
'MACHINE\ASPNET' was used

No SQL was included in the asp.net code except for calling stored
proccedures; the policy is to only call stored procedures within the
asp.net pages and encrypt the connection strings inside the web.config
file.

Kindly, give me some guidelines for better security or discuss with me
the security aspects i mentioned

Regards

coosa (coosa76@gmail.com) writes:
> I'm designing a system including the database and the security
> represents the most crucial aspect of the system; hence for the
> database security i have implemented the following aspects and need
> your advise on further aspects or perhaps corrections where by the
> system is web based using asp.net and under iis 6.0 with https; in the
> asp.net engine side, i have included client-side validations for what
> ever is inputed and validated against sql injections for postback
> forms:
> The features of security in SQL Server 2005 side i have implemented:
> 1.) Created MACHINE\ASPNET Account
> 2.) Allowed ASPNET Account to access the DB
> 3.) Explicity denied ASPNET Account all permissions to all tables,
> functions and views
> 4.) Denied all permissions to the ASPNET user for stored procedures
> except EXECUTE permissions
> 5.) By Stored procedure creation, WITH ENCRYPTION, EXECUTE AS
> 'MACHINE\ASPNET' was used
>
> No SQL was included in the asp.net code except for calling stored
> proccedures; the policy is to only call stored procedures within the
> asp.net pages and encrypt the connection strings inside the web.config
> file.

What you don't say is how you call the stored procedures. You should
be using CommandType.StoredProcedure. If you use EXEC statements, you
may still be open to SQL injection.

I'm not sure that I see the point with the EXECUTE AS clause, but as
long as all connections are by the ASPNET user, it should matter much
anyway.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx