SQL Server Authenticated Users, why are we still using them?

  • Comments posted to this topic are about the item SQL Server Authenticated Users, why are we still using them?

  • If your application runs on Linux but has a SQL Server back end does anyone know how to get it to use AD?  I know some apps allow it through a library called krb5 but don't know the specifics.
    I think this is another area that forces DBAs or domain admins to become policemen.  In the security sphere I feel that too much is left to best endeavours with unrecorded lines of responsibility.

  • What keeps me from using it? Linux and Python.

    I use a lot of Python for my ETL as opposed to SSIS in Azure. Even before I moved to Azure, I still used a lot of Python with SQL Server and with SSIS to streamline data into the system. For example, most of my data comes from various API's that Python is used to load data parallel directly into the database.

    I do have AD integration with my Azure solution today. But with the IP based firewall and certificates, it makes securing those authenticate users better.

  • David.Poole - Friday, March 30, 2018 12:32 AM

    If your application runs on Linux but has a SQL Server back end does anyone know how to get it to use AD?  I know some apps allow it through a library called krb5 but don't know the specifics.
    I think this is another area that forces DBAs or domain admins to become policemen.  In the security sphere I feel that too much is left to best endeavours with unrecorded lines of responsibility.

    I don't know personally, but the organization I work for has a global IT team that manages our infrastructure. They have a method where if you're using Linux, they can get AD integration working on the system. But this has some cons that I do believe most Linux users hate with their apps.

  • We have a MS application that still requires SQL authentication.

  • Creating a linked server connection to another domain.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Like others I have SQL Servers that are off the domain for security reasons.

  • I see your point if you live in a world where all your users are part of your domain.  But if you serve up applications that connect to SQL Server from hundreds (in our case thousands) of users who are NOT in your domain, you have two choices.  Use SQL Server authentication, or use one (or a few) logins and do your own login and password control, which may or may not be as good or better than what SQL Server offers.  I'm open to viewpoints!


    Student of SQL and Golf, Master of Neither

  • BobAtDBS - Friday, March 30, 2018 7:20 AM

    I see your point if you live in a world where all your users are part of your domain.  But if you serve up applications that connect to SQL Server from hundreds (in our case thousands) of users who are NOT in your domain, you have two choices.  Use SQL Server authentication, or use one (or a few) logins and do your own login and password control, which may or may not be as good or better than what SQL Server offers.  I'm open to viewpoints!

    I am assuming that if you have thousands of users you are talking about a web app?  If you are, normally websites running under IIS will have their own application pool.  You can set the identity of the application pool to be a network user.  Normally, we get our AD guys to create a service account, then all access to the web app happen through this one AD account.

    Ben

  • bkubicek - Friday, March 30, 2018 7:52 AM

    BobAtDBS - Friday, March 30, 2018 7:20 AM

    I see your point if you live in a world where all your users are part of your domain.  But if you serve up applications that connect to SQL Server from hundreds (in our case thousands) of users who are NOT in your domain, you have two choices.  Use SQL Server authentication, or use one (or a few) logins and do your own login and password control, which may or may not be as good or better than what SQL Server offers.  I'm open to viewpoints!

    I am assuming that if you have thousands of users you are talking about a web app?  If you are, normally websites running under IIS will have their own application pool.  You can set the identity of the application pool to be a network user.  Normally, we get our AD guys to create a service account, then all access to the web app happen through this one AD account.

    Ben

    Why would you assume that it's a web app because there are thousands of users?

  • I'm a bit new to the security bits of SQL Server administration but I thought that SQL Authentication mode could be forced to use SSL encryption.

  • Thanks for the walk down Memory Lane!

  • lmalatesta - Friday, March 30, 2018 8:00 AM

    bkubicek - Friday, March 30, 2018 7:52 AM

    BobAtDBS - Friday, March 30, 2018 7:20 AM

    I see your point if you live in a world where all your users are part of your domain.  But if you serve up applications that connect to SQL Server from hundreds (in our case thousands) of users who are NOT in your domain, you have two choices.  Use SQL Server authentication, or use one (or a few) logins and do your own login and password control, which may or may not be as good or better than what SQL Server offers.  I'm open to viewpoints!

    I am assuming that if you have thousands of users you are talking about a web app?  If you are, normally websites running under IIS will have their own application pool.  You can set the identity of the application pool to be a network user.  Normally, we get our AD guys to create a service account, then all access to the web app happen through this one AD account.

    Ben

    Why would you assume that it's a web app because there are thousands of users?

    In my opinion, if you have thousands of users, it is way easier to manage application updates and controlling data access with a web app.  A web server is a lot easier to scale for better performance.  Of course, a web app would require network or internet connectivity, but in most cases this is somewhat of a given.

    I would guess since you asked the question that you have an application with thousands of users and it is not a web app?

    Ben

  • lmalatesta - Friday, March 30, 2018 8:04 AM

    I'm a bit new to the security bits of SQL Server administration but I thought that SQL Authentication mode could be forced to use SSL encryption.

    So even if you force SSL that is just the connection between the user's computer and the sql server.  In most cases the connection string is hard coded in a config file someplace on the user's computer.  There isn't really anything keep the user or someone else from opening up the config file in notepad and see the username and password to access the SQL server.

    Ben

  • bkubicek - Friday, March 30, 2018 8:10 AM

    lmalatesta - Friday, March 30, 2018 8:04 AM

    I'm a bit new to the security bits of SQL Server administration but I thought that SQL Authentication mode could be forced to use SSL encryption.

    So even if you force SSL that is just the connection between the user's computer and the sql server.  In most cases the connection string is hard coded in a config file someplace on the user's computer.  There isn't really anything keep the user or someone else from opening up the config file in notepad and see the username and password to access the SQL server.

    Ben

    That's a fair point. I don't know if I would say that "in most cases" though.

Viewing 15 posts - 1 through 15 (of 49 total)

You must be logged in to reply to this topic. Login to reply