Accessing SQL Server Data: Best Practices

[zachattack05]

Good morning everyone!

I haven't been here in a while, but I have a question that I thought someone here might be able to help with.

When it comes to accessing data from a SQL server, I know the logistics of getting that data through code, no issues there, but is it safe (or rather, smart) to have your application interact directly with the SQL server? As in, not using a middle-man or an additional tier?

I've toyed with N-Tier data applications and I find them highly annoying and complicated as well as convoluted.

I know if you run SQL on a web server, and your website talks to the SQL server, that's fine to do (since outside access to the server isn't needed, your application is the only one that can access the server) but with a desktop application, that's not the case, a user could be in Tokyo and another in Los Angeles all accessing the same database.

I guess I'm asking...if my application connects directly to a SQL server, are there any security issues that I need to be aware of?

[JorgeM]

I've toyed with N-Tier data applications and I find them highly annoying and complicated as well as convoluted.

My opinion is always design for simplicity and add complexity only if the business requires it. Designing N-Tier solutions isnt always the correct design for every solution.

I know if you run SQL on a web server, and your website talks to the SQL server, that's fine to do (since outside access to the server isn't needed, your application is the only one that can access the server)

Well, that's only true if your server is blocking inbound access to port 1433 (SQL) and/or no other ports are open for communication with that server.

but with a desktop application, that's not the case, a user could be in Tokyo and another in Los Angeles all accessing the same database.

true, but that is fine and acceptable.

if my application connects directly to a SQL server, are there any security issues that I need to be aware of?

Yes, of course. To allow remote connections into the SQL server, you need to have the appropriate port listening.

If you wanted to reduce the risk, one approach is that you would only allow certain IPs inbound through the firewall. For example, say your desktop application is accessed by a limited group of people. It would be appropriate to create a firewall rule to only allow that group access from the remote location. If you have a large number of remote users, or they are mobile (their IPs change), then this isnt feasible. In addition to SQL, there may be other entry points to gain access to the server, and control it.

In additional to perimeter security, you have to ensure your servers are physically secured, user accounts and passwords secured, operating system and application patches up to date, fix other vulnerabilities on the server, etc, etc... Any time you expose a server to a public, untrusted network (Internet), you incur risk. You simply have to determine how much you are willing to do to mitigate those risks. As you approach 100% mitigation, the costs are exponential.

[zachattack05]

Thanks JorgeM.

I'm just paranoid about security and with the application, it could be widely distributed and used by people I wouldn't necessarily know so setting up users and IP restrictions isn't an option.

I'll have to look into this more. I might audit a class on SQL server security at the local university. To me, setting up users just seems too easy, and I'm worried that I would miss something.