-- 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
- Install SQL server on linux
After that's been done I have the following tasks:
- Whenever a new hosting account is created, I hook into this event and
Create a new SQL userwith a
new databaseassigned to this user. Account creation is not a manual process, so this can run
ntimes per day, unattended.
- I will add said database to a script that
performs backupsautomatically using
- 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?