1

I have a server which was joined to an AD Domain, then installed with SQL Server 2008 R2 and System Center Operations Manager 2012's Reporting Server on it. The same server had the Active Directory DS, CS, DNS Server, IIS Server installed.

Now someone just removed all the role from this server which includes AD DS, AD CS, IIS, DNS, all gone, what only remain is the SQL Server only. To solve the issue, I installed the IIS Server on it, however instead of installing ADDS here, I have installed it to another machine with Windows 2012 R2, the new machine has AD DS, AD CS and DNS Server.

During the System Center(SCOM) install, I created some users on the old AD for the SCOM, three users and all users on a group named OMAdmins. (where the DB is also installed). They had different permissions to SQL Server and to SCOM (SCOM Management server and console are installed on another machine, which too unfortunately losses its integration to old AD).

Now I re-created the same users, same groups, with same old password on new separate AD machine I have, and authenticated all the services with the right users on the SQL Server machine and on SCOM machine.

My issues are: I cannot take any backup of DB, I was able to login to SQL Server Management Studio using NT Authority, but when I try to do backup, it says permission denied, when I check the Security -> Logins on the Studio, I see many users are missing (those SCOM Users, the groups those user was on, some NT Authority and the SCOM computer which was also on the AD). I only see two users now BUILTIN/Administrator and sa. I tried to add new users to add those missing users from AD, but SQL server fails with error message on permission denied.

Any help to bring my server back> I was able to start the services with new AD user's authentication but it looks like it is just a forced authentication. The original authentication SQL server had is missing. I need some to help to get it back to work and so for the SCOM which is also down too. I even tried SCOM Management Server recovery, but it failed and the log says SQL connection was established but existing database is already there as error, so cannot proceed with the recovery of SCOM either. It is the SQL Server which first needs an urgent fixes or recovery.

  • It looks like your login has limited privileges on SQL Server. That's why you are not seeing all the logins. Try logging in SQL Server with sa account and you should be able to see all the logins in there. – SQLPRODDBA May 31 '16 at 3:23
  • Is there any way I can re-integrate the sql server to the new active directory as domain name, AD sql user name and password all are same on new AD server. I want to make things like before. – Manjurul Islam May 31 '16 at 4:58
  • If you have the sa account password, you can get all the old login details. You just need to copy/paste the logins and permissions for new users using the old login details. I am assuming you have the sa password or any login with sa privileges for this to work. – SQLPRODDBA May 31 '16 at 5:04
  • I'll put some more details about sa login later, but as far I remember, I didn't set any sa user password, I first created the AD user, then installed sql server using the ad user's credentials on Windows login. I mean I didn't install the sql server under windows administrator account, installed it under the AD user's profile . I have added the AD user group on that machine's Builtin\Administrator. Still I will check with a password I remember. – Manjurul Islam May 31 '16 at 5:18
  • If you don't have sa password or any other login with sa privileges, you can add one by following below link: blogs.technet.microsoft.com/sqlman/2011/06/14/… – SQLPRODDBA May 31 '16 at 5:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.