Connexion sécurisée c# Web App pour SQL Server - Stack Overflow

I was wondering what the best approach to make secure connection to SQL Server would be? Here is my scenario. At my work, we have SQL Server 2012 Standard. My boss wanted me to create a new DB utilizing TDE. I found out that you have to have Enterprise Edition in order to use TDE. We looked into it and it was going to cost a fortune, so we are not going to purchase Enterprise Edition. So I was thinking about using Stored Procedures to interact with DB. Is this more secure than submitting SQL query across web? Also, what is the best security measure to communicate and transfer data to/from web app/DB server?

Thanks in advance,

Brad

EDIT: Also, is there anyway to securely send username/password credentials in the connection string?

Stored procedures would in a sense be more secure, since you could simply submit objects into the procedure to generate your desired result. This would mask the underlying SQL statement, so it could be considered more secure. I think most places rely on the Windows Authentication aspect of SQL in a domain environment.

It is fairly secure, more so if your site is wrapped up in SSL. Avoid standard SQL authentication, it's text based and shouldn't really be considered.

Code wise, you probably want a layer in between your DB and your website to do all the heavy lifting. This somewhat obfuscates what your website is doing since it is calling to your middle-man, and he handles all the truly transactional stuff.

Also, how are users going to be interacting with your website? Will they be required to login first, and what mechanism will control this? There are quite a few other design details to figure out before you can really consider which method will be the best balance of security and usability. I'd go for WindowsAuth/SSL and utilize a security account to perform all your transactions. It's easy to setup and AFAIK not easy to hack.

This are two different things - TDE will help you just with encrypting data on file system (so if I have access to filesystem where you have your db I won't be able to read it if you're using TDE).

Communication between application and db is different issue. There are several things you can do:

• open network ports for db just to webserver (only from web server ip(s) you can access db)


• use integrated authentication (no-one can sniff your password)


• embed your business logic into stored procedures (you limit access to db just to function needed for scope of your web application)

However especially the stored procedures part can be pain (ORM like EF, LinqToSQL or nHibernate are just terrible when it comes to stored procedures). And also this approach doesn't guarantee that no-one will be able to see data coming from database server to web server).

If sniffing data between webserver and db server can be a problem, you have to write webservice for accessing data. This webservice should be on trusted network to db server (as close to db as it can be - same box is the best). Webserver should call this webservice over https (thus sniffing data between web server and webservice is impossible) and use authentication to access webservice (recommended is windows authentication).

I was wondering what the best approach to make secure connection to SQL Server would be? Here is my scenario. At my work, we have SQL Server 2012 Standard. My boss wanted me to create a new DB utilizing TDE. I found out that you have to have Enterprise Edition in order to use TDE. We looked into it and it was going to cost a fortune, so we are not going to purchase Enterprise Edition. So I was thinking about using Stored Procedures to interact with DB. Is this more secure than submitting SQL query across web? Also, what is the best security measure to communicate and transfer data to/from web app/DB server?

Thanks in advance,

Brad

EDIT: Also, is there anyway to securely send username/password credentials in the connection string?

Stored procedures would in a sense be more secure, since you could simply submit objects into the procedure to generate your desired result. This would mask the underlying SQL statement, so it could be considered more secure. I think most places rely on the Windows Authentication aspect of SQL in a domain environment.

It is fairly secure, more so if your site is wrapped up in SSL. Avoid standard SQL authentication, it's text based and shouldn't really be considered.

Code wise, you probably want a layer in between your DB and your website to do all the heavy lifting. This somewhat obfuscates what your website is doing since it is calling to your middle-man, and he handles all the truly transactional stuff.

Also, how are users going to be interacting with your website? Will they be required to login first, and what mechanism will control this? There are quite a few other design details to figure out before you can really consider which method will be the best balance of security and usability. I'd go for WindowsAuth/SSL and utilize a security account to perform all your transactions. It's easy to setup and AFAIK not easy to hack.

This are two different things - TDE will help you just with encrypting data on file system (so if I have access to filesystem where you have your db I won't be able to read it if you're using TDE).

Communication between application and db is different issue. There are several things you can do:

• open network ports for db just to webserver (only from web server ip(s) you can access db)


• use integrated authentication (no-one can sniff your password)


• embed your business logic into stored procedures (you limit access to db just to function needed for scope of your web application)

However especially the stored procedures part can be pain (ORM like EF, LinqToSQL or nHibernate are just terrible when it comes to stored procedures). And also this approach doesn't guarantee that no-one will be able to see data coming from database server to web server).

If sniffing data between webserver and db server can be a problem, you have to write webservice for accessing data. This webservice should be on trusted network to db server (as close to db as it can be - same box is the best). Webserver should call this webservice over https (thus sniffing data between web server and webservice is impossible) and use authentication to access webservice (recommended is windows authentication).

Source