Scheduled backup 2014 exp

Question

Scheduled backup 2014 exp

DubbySwords

SSC Enthusiast

Points: 127
More actions
December 3, 2015 at 4:40 am

#306853

I'm using SQL server 2014 express.
I'm now trying to shedule a backup.
I'm using an maintenance account which ha rights in the database and the server.
The problem is I do not have rights when I use the scheduled task.
>sqlcmd -S .\SQLEXPRESS_SP1 -d RESULT_DB -U UserID
Password:
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login failed
for user 'UserID'..
Any help greatly appreciated.
/D

Viewing 7 posts - 1 through 7 (of 7 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

If you're scheduling a backup, you need to provide the password in the connection string - you can't pass it in interactively.

The error message isn't saying you don't have rights - it's saying the login failed. That could be because UserID doesn't exist, the password is wrong, RESULT_DB doesn't exist, or a number of other reasons. You can get more information from the SQL errorlog - provided you're logging failed logins.

John

DubbySwords SSC Enthusiast Points: 127 More actions · Answer 2

Thanks for the quick reply. I checked out the logs and found

Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: <local machine>]

UserId is a windows account, what is SQL Server Authentication?

I have an account on the sql server but with no password given ie windows athentication.

/D

Johan Bijnens SSC Guru Points: 135553 More actions · Answer 3

to use Windows authentication use the parameter -E in stead of the -U and -pwd

ref:https://msdn.microsoft.com/en-us/library/ms162773.aspx

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

DubbySwords SSC Enthusiast Points: 127 More actions · Answer 4

Yes thats one solution but I want to do as a task for another user, -E does not seem to work in this case.

/D

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

If you're doing it from a command line, use RunAs to run as a different user. I've a feeling that only works interactively, however - you'll need to investigate.