Automated backup with no Agent i.e. SQL Express. Hide logon details

Question

Automated backup with no Agent i.e. SQL Express. Hide logon details

terry999

SSCertifiable

Points: 5036
More actions
June 10, 2013 at 4:42 pm

#278399

Hi
http://support.microsoft.com/kb/2019698
Shows a SP to backup DB(s) and how to use windows scheduler to achieve this.
This would work ok if you could use Windows Authentication, however if the only user you have is an SQL login, the SQLCMD line parameter will contain login credentials i.e. password.
I tried creating the backup DB SP with EXECUTE AS but would effectively have to have this on every DB because of reasons explained in http://www.sommarskog.se/grantperm.html
when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.
This could be done with certificates and proxy users - but is OVER my head. It SEEMS difficult to maintain on many client machines.
I can only think of creating an EXE (binary code) that is called by Windows Scheduler and connects to the DB Server with backup rights. The Login credentials would be hardcoded but safe in binary form.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Andrew G SSChampion Points: 12809 More actions · Answer 1

Not sure if this is what you're after, but here http://zainuvk.blogspot.com.au/2011/09/hise-username-and-password-with-sqlcmd.html

discusses using a Windows environment variable to input a default user and password so that they're not specified as command line arguments. However you still need to create those environment variables at some stage... and the user can still get the plain text password from the variable. Dont think there is any way around it.

Could you create a stored proc, eg: CREATE PROC dbo.backupDbs WITH EXECUTE AS 'sa' BEGIN sp_msforeachdb 'BACKUP DATABASE blah TO blahblahblah'' END

GRANT EXECUTE ON dbo.backupDbs TO windows\user

Then use integrated security (-E switch in sqlcmd)?

terry999 SSCertifiable Points: 5036 More actions · Answer 2

Thanks will check link out.

If I create the SP logged in as SA in the master DB with EXECUTE AS OWNER.

I still get The server principal "sa" is not able to access the database "tstDB" under the current security context. It is expected behaviour - can't quite get my head around it but is explained in the article.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

why cant you create a windows account and use that?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

terry999 SSCertifiable Points: 5036 More actions · Answer 4

Thats what I've suggested - but trying to see all methods.

the SQLExpress is installed on many client machines. I don't know if the install guys can create windows users easily. I think the domain may change between different installs.