1

-- old question --

I'm building a multi-tenant SQL Server for Linux solution for hosting companies. Basically end-users will get access to SQL server with their own user account.

Now, I want to be able to backup their databases every day using the SQL Commandline tools. This is no issues, except for the fact that I don't know the password set for the user's account to login into SQL, AND I don't know the SA account password (as my clients choose this themselves).

Is there any way to log into SQL server as the 'machine' account (much like the local accounts that you can use on the Microsoft OS) to login so I can set up unattended backups through scripting without needing to know passwords for these users ?

I checked SQL Server Management Studio, connected to the Linux box and saw there is a user called BUILTIN\Administrators, but I have no idea how to login with this user to SQL, I tried logging in using root+password, but this doesn't work.

-- Altered question --

I'm building a plugin for a hosting panel (like plesk). This plugin allows hosting customers (end users) to use SQL Server for their hosting needs within their own environment. My real customer is the server owner (hosting company). THey need to

  1. Install SQL server on linux

After that's been done I have the following tasks:

  1. Whenever a new hosting account is created, I hook into this event and Create a new SQL user with a new database assigned to this user. Account creation is not a manual process, so this can run n times per day, unattended.
  2. I will add said database to a script that performs backups automatically using cronjobs
  3. The (hosting customer) will be able to create new "databases" from their hosting control panel on demand (self service). In practise we will validate the user account, and create a new database on their behalf (using a shell script with SQL commandline)

For all actions specified above I will need a sysadmin user to perform these tasks. I can't prompt the hosting company for the SA password every time I need to perform something (as the processes above are unattended), and it's bad practise to save/store the SA password somewhere in a text file.

How can I execute options 2-3-4 without storing the SA password? Should I create a secondary sysadmin account with a password that's generated by the machine?

I'm open-sourcing this plugin when I'm done, and even if we use generated passwords, people will be able to find them out (we need to store it, somewhere).

SQL Agent won't work (as I don't have the SA password), and some actions can't be run by SQL agent as they are 'on-demand' (point 2 and 4 above)

Any tips on my revised question?

Improve this question
9
  • Just create your own sysadmin account during the instance configuration and use that for administrative functions. No need to use the sa account.
    – Dan Guzman
    Jun 2, 2018 at 12:11
  • @DanGuzman the issue I have is this will result in me generating a password somewhere, use it as a sysadmin account, and storing the password on disk somewhere because the backup job will have to use this password. My solution will be installed by server owners (not on my own servers) so it's not like I can just use the same SA password everywhere. Imho it's not considered very safe if I save the password on disk.
    – Erik J.
    Jun 2, 2018 at 14:04
  • So you're wanting to log in with a privileged account, but not use a password? That would be a huge security issue if that was possible. You need to rethink the solution.
    – Tony Hinkle
    Jun 2, 2018 at 17:25
  • On Windows we have Active Directory authentication (or windows authentication), I had hoped to do the same thing here in linux, but it seems I might need to rethink what I'm doing here.
    – Erik J.
    Jun 2, 2018 at 19:23
  • 1
    Why not just use SQL Agent?
    – David Browne - Microsoft
    Jun 3, 2018 at 0:12

1 Answer 1

Reset to default
0

I checked in with the WHM+cPanel - the company behind the hosting control panel and they said they store their MySql root user password on a local file on disk as well for doing all the automated maintenance, creation etc.

Excerpt from their docs: By default, cPanel & WHM's implementation of MySQL stores the MySQL root account password in the /root/.my.cnf configuration file. Do not edit this file manually.

I'm going to end up with doing the same, and storing the file (read only) in the same /root/ folder.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.