Thursday, March 29, 2012

Credentials in Data Sources are getting locked out of SQL

Weird one this....

Created a report. Set up a new datasource. Using credentials securely stored in server. Set up a local user on the SQL Server specifically to reference through this datasource.

Go to reportserver, run the report, and it runs fine. Run it a couple more times, runs fine. Then, all of a sudden, it fails, with the error message

  • The referenced account is currently locked out and may not be logged on to. (Exception from HRESULT: 0x80070775)

    This is very strange. I check the account on SQL, and it's still enabled. As it's not a domain account, it hasn't been disabled on the domain. I'm not even familiar with the phrase 'locked out'. After a bit of checking I find out about maxInvalidPasswordAttempts, whereby ASP 'locks out' accounts after 'n' wrong password attempts.

    Firstly, I'm not entering the password wrong. It's correctly entered, and stored in the report server.

    Secondly, where the heck does RS get off locking out accounts without my permission?

    I have no idea how to unlock these accounts.

    If I go ahead and create another account on the SQL, exactly the same thing happens. I can reproduce this at will. It works the first couple of times, then bang, 'locked out' again.

    Anyone got any ideas on how to modify this behavoir?
    I am getting the same error, all of a sudden, does anyone have a clue how to fix this?|||I am having the same problem. Does anyone know if this is a SP2 problem? Please help!

    |||Hi
    How did you create a user to access the database and this user is not a domain user? If this user is not a sql login user then the OS will lock ou the user and thus the dB lockout. If the user is a non domain user then the OS is trying netuse the account and this will definately lock it out.

    gatharia
    |||

    |||

    I've solved part of my troubles. I had set up a shared schedule and accidentally was using the default recurrance pattern to start every 10 minutes. This can happen very easily, as when you go to test something you might want to run it once. So... you create a new shared schedule (so you don't end up running everything on your production shared schedule). I don't know why the default is set to this recurrance pattern instead of once. Anyway... I was having the same problem as Sam Loud, but it was not caused by what I was currently doing. It was caused by this recurring "thing" that was running with the wrong password.

    Another "Thing" that caused this problem is when I tried to use shared data sources. As far as I can tell, you cannot use shared data sources when trying to use subscriptions to fileshare. I think this is a bug with reportserver. The first time it runs, it will run successfully. The second time it runs, you will get the RSLogonFailed. I don't know any way around this problem other than to just not use shared data sources.

    The other thing that has caused me problems is that when I modify a report and deploy it, make sure not to overwrite the datasource (note: this is the default setting in IIS). If you overwrite the datasource, then the userid and password that you set up on the datasource on the server gets wiped out and everything bombs out on logon and again you have a lockout situation.

    I am still having some lockouts and still working on this problem. If anyone else can let me know what their experiences are that would be very helpful. I am using subscriptions to fileshare with about 200 reports running on various schedules. Thanks!

    |||

    The final (and most difficult to resolve) problem that was causing the "logon Failed" and then account lockout problem(s) was related to the windows account that expired (it was set up to expire in 90 days). Since I used this userid when setting up my report subscriptions, all of my subscriptions failed with invalid login and then the account was locked out. When the account userid was reactivated in WINDOWS with the same password, still all of the subscriptions failed on login. This was very frustrating because the main report services account was getting locked every day and nobody could get to the report server and all the report jobs were failing. I tried going into each report subscription and retyping the userid and password (which were valid), but I still got the invalid login. After 3 weeks of frustration and trying different things, I have discovered that what you have to do is 1) go in to the subscription general screen and change the userid and password to something invalid and totally different 2) go out of the subscription general screen (to save your changes) and 3) go back into the subscription general screen and re-type the correct userid and password. This resolved all of my login and lockout problems with the subscriptions and writing to datashare. I hope that this helps anyone else out there who might be struggling with this same issue/problem with reportserver and subscriptions. It is obviously a bug.

  • No comments:

    Post a Comment