locked
Best Practices for Data Access RRS feed

  • Question

  • Good morning!

    I was wondering if someone might give me some advice on some best practices for retrieving data from a SQL server in the cloud via a desktop application?

    I'm curious if I embed into my desktop application the server address (IP, or Domain or whatever) and allow the users to provide their own usernames and passwords when using the application, if there was anything "wrong" with that? Where-in my application collects the username and password from the user, connects to a server with that username and password, retrieves the data and uses it in-app.

    I'm petrified of security issues and I would hate to start using a SQL database with this setup only to find out that anyone could download x, y or z and connect to the database and see everything.

    Assuming I secure all of the users with limited permissions, is there anything wrong with exposing a SQL server to the web for my application to use? If so, what and what would be a reasonable alternative?

    I really appreciate any help and feedback!

    Tuesday, May 20, 2014 4:37 PM

Answers

  • For example, can I do anything to tell SQL "If an application with token XXXXXXX connects, that's okay, all the others deny access, even if the username and password are correct?"

    No. You can look at app_name() as Uri suggested, but it is trivial to work around it. If you only want to stop users who are too smart for their own good, that may be good enough. For an instance you expose on the Internet, no way.

    If you only want users to access the database through the application, you need to architect for this. This means, you need to have a middle layer somewhere. Either an application server, or something like Terminal Server which they log on to and come directly into the application. Or reimplement the application as a web app.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Wednesday, May 21, 2014 9:41 AM
    • Marked as answer by Sofiya Li Tuesday, May 27, 2014 1:48 AM
    Wednesday, May 21, 2014 7:07 AM
  • Perhaps this link helps you

    http://www.simple-talk.com/sql/sql-server-2005/logon-triggers/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Sofiya Li Wednesday, May 21, 2014 9:41 AM
    • Marked as answer by Sofiya Li Tuesday, May 27, 2014 1:48 AM
    Wednesday, May 21, 2014 5:47 AM
  • Hi Zach,

    You can use a logon trigger, as Uri suggested, to check the application name of the connection and only allow connections from a specific application to pass through. The problem is that any developer can change the application name and put there whatever she wants, so it's going to be quite easy to impersonate your "allowed" application and gain access to your database.

    --------------------------------------------
    Guy Glantser
    SQL Server Consultant & Instructor
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • Proposed as answer by Sofiya Li Wednesday, May 21, 2014 9:41 AM
    • Marked as answer by Sofiya Li Tuesday, May 27, 2014 1:48 AM
    Wednesday, May 21, 2014 5:56 AM

All replies

  • The alternative would be to have a local application server that sits in between the Internet and SQL Server.

    Whatever server you expose on the net, there will be processes knocking on the door constantly. But they may be more interested in an SQL Server instance, which they have more experience on cracking.

    If you expose SQL Server on the internet, you must disable the sa account, and make sure that all SQL logins have only basic permission. All accounts you use for admins should be Windows logins. (SQL login would be subject to brute-force attacks.)

    Also keep in mind that users may get the idea to connect to your instance through SSMS or similar and not necesarily your desktop application.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 20, 2014 9:39 PM
  • Erland,

    Thanks for the reply!

    I know this may be a long shot, but is there a way to have the SQL server become aware of the application it is allowing connections from?

    For example, can I do anything to tell SQL "If an application with token XXXXXXX connects, that's okay, all the others deny access, even if the username and password are correct?"

    That way users would not be able to connect to SQL with SSMS or whatever other browser they might have or use.

    I have a feeling the answer to that is no, but I figured I would ask.

    Wednesday, May 21, 2014 1:29 AM
  • Perhaps this link helps you

    http://www.simple-talk.com/sql/sql-server-2005/logon-triggers/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Sofiya Li Wednesday, May 21, 2014 9:41 AM
    • Marked as answer by Sofiya Li Tuesday, May 27, 2014 1:48 AM
    Wednesday, May 21, 2014 5:47 AM
  • Hi Zach,

    You can use a logon trigger, as Uri suggested, to check the application name of the connection and only allow connections from a specific application to pass through. The problem is that any developer can change the application name and put there whatever she wants, so it's going to be quite easy to impersonate your "allowed" application and gain access to your database.

    --------------------------------------------
    Guy Glantser
    SQL Server Consultant & Instructor
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • Proposed as answer by Sofiya Li Wednesday, May 21, 2014 9:41 AM
    • Marked as answer by Sofiya Li Tuesday, May 27, 2014 1:48 AM
    Wednesday, May 21, 2014 5:56 AM
  • For example, can I do anything to tell SQL "If an application with token XXXXXXX connects, that's okay, all the others deny access, even if the username and password are correct?"

    No. You can look at app_name() as Uri suggested, but it is trivial to work around it. If you only want to stop users who are too smart for their own good, that may be good enough. For an instance you expose on the Internet, no way.

    If you only want users to access the database through the application, you need to architect for this. This means, you need to have a middle layer somewhere. Either an application server, or something like Terminal Server which they log on to and come directly into the application. Or reimplement the application as a web app.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Wednesday, May 21, 2014 9:41 AM
    • Marked as answer by Sofiya Li Tuesday, May 27, 2014 1:48 AM
    Wednesday, May 21, 2014 7:07 AM
  • Another followup question Erland if I may:

    You said to disable the sa account, which I will definitely will be doing, but if the SQL server is located on a VM running on Azure, other than logging in with remote desktop, is there a way for me to connect to my SQL instance on the VM using SSMS from my local machine using Windows Authentication?

    I guess I could create a domain controller and join the domain, but that seems a little over the top.

    In short, is it possible for me to connect to SQL using Windows authentication remotely without having to use Remote Desktop? I'm assuming the answer is no, but thought I would ask. :)

    Thanks everyone for your help! I really appreciate it!

    Tuesday, May 27, 2014 5:14 PM
  • There are two options, none of them very palatable:

    1) One is to create a domain, and add the VM and your local box to it.

    2) Stick to a workgroup, but have the same user name and password on both machines.

    In practice, a better option is to create an SQL login that is member of sysadmin - or who have rights to impersonate an account that is member of sysadmin. And for that matter, you could use the built-in sa account - but you rename it to something else.

    The other day I was looking at the error log from a server that apparently had been exposed on the net. The log was full with failed login attempts for sa, with occasional attempts for names like usera and so on. The server is in Sweden - the IP address for the login attempts were in China.

    Just so know what you can expect.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 27, 2014 9:13 PM
  • Erland,

    You have been extremely helpful! I really appreciate all of your time and info!

    I think I know what I will be doing now.

    Just out of curiousity though, once I have things set-up, but before I start distributing the information to users, is there an application I can download or something to check the security of the setup? You know, kind of like a scanner type thing that will try to find weaknesses in my setup (not disabling the sa account for example, or using a bad port number, or having a service running that isn't recommended (like the Browser service) etc...)

    I know there are services you can hire to do a security check, but I am sure they cost a pretty penny.

    Again, thank you so much for your help!

    Thursday, May 29, 2014 1:41 PM
  • There might be, but it's definitely outside my knowledge.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, May 29, 2014 3:44 PM
  • Thanks Erland,

    You have been extremely helpful!

    Monday, June 2, 2014 8:06 PM