i put myself in a big problem

IT-ADMIN

the SQL server is installed on the application server, this application server was before a stand alone server and joined to domain also but the company that install the payroll software on the application server didn't use domain account, they created local admin account on the server application because they do remote support for us sometimes and they know the password of this local admin account (in order not to give them a domain admin account for our security they created local admin account to work with)
tomorrow i will contact them to see this issue, i'm sure they will blame me for deleteting those account

PSX_Defector

@scottalanmiller said:

@IT-ADMIN said:

since i have a connection error, it means that the connection use local account, because all local acconts were deleted (when i go to users and groups i found only 2 account : administrator and guest)

I am not aware of using local accounts for SQL Server. The SQL Server runs on the box that you put the Domain Controller on or on a separate server?

There are two, very misleading types of accounts with SQL. Local and Windows Authentication. Local means SQL only, stored in the master security table. Windows authentication means that it's setup to read the GUIDs of IDs within Windows, be it local or domain. You have to add them in separately.

IT-ADMIN, if you have the sa account, you might be able to pull yourself out of the fire. Get the logs, find out what needs to be recreated, then you will have to rebuild the accounts by hand and reset everyone who might have been accessing it. Certainly better than the current hands in the air pants on fire situation.

coliver

Wouldn't you be able to demote the application server? That should bring the local admin accounts back.

scottalanmiller

@IT-ADMIN said:

the SQL server is installed on the application server, this application server was before a stand alone server and joined to domain also but the company that install the payroll software on the application server didn't use domain account, they created local admin account on the server application because they do remote support for us sometimes and they know the password of this local admin account (in order not to give them a domain admin account for our security they created local admin account to work with)
tomorrow i will contact them to see this issue, i'm sure they will blame me for deleteting those account

Why would they need a domain admin account? A domain account that only has needed access on that machine would make a lot more sense, IMHO. Making local accounts doesn't seem to make any sense, even for the situation described.

scottalanmiller

@coliver said:

Wouldn't you be able to demote the application server? That should bring the local admin accounts back.

Not if they were deleted.

Dashrender

@scottalanmiller said:

@coliver said:

Wouldn't you be able to demote the application server? That should bring the local admin accounts back.

Not if they were deleted.

Exactly, when you promote a server to a DC the local SAM system gets deleted.

Dashrender

@scottalanmiller said:

Why would they need a domain admin account? A domain account that only has needed access on that machine would make a lot more sense, IMHO. Making local accounts doesn't seem to make any sense, even for the situation described.

I'm guessing they didn't consider that option - non Windows Admins (in this case AKA SQL admins) probably don't think about how a domain user can have local admin rights.

IT-ADMIN

to be honest with you i don't know much about SQL server and its account, but one thing is sure that the SAM accounts were deleted and these accounts has a direct relation with SQL server connection, how this relation i don't know
the proof that these local account have relation with SQL server is that before promoting the sever everything was fine, as soon as i promote the damn server the connection problem occured
after demoting the server, it was too late because all local accout were deleted except administrator and guest accounts

scottalanmiller

@IT-ADMIN said:

to be honest with you i don't know much about SQL server and its account, but one thing is sure that the SAM accounts were deleted and these accounts has a direct relation with SQL server connection, how this relation i don't know

Makes sense, they set up non-domain local accounts. Very unprofessional IMHO. Not something I would expect a consultant to be doing. Rather poor.

I'm afraid that you need to make new accounts and set things up new or go to a backup.

scottalanmiller

@IT-ADMIN said:

after demoting the server, it was too late because all local accout were deleted except administrator and guest accounts

Correct, the accounts are deleted, deleted means that they can't come back. THey were not disabled, they were actually deleted.

IT-ADMIN

really i'm lost with this now, i will wait the support guy tomorrow to see how we can set this up,
anyway i will be blamed for this, because i do it without any approval from the management because i never thought that this would cause a problem,

Dashrender

SQL has a special account called the SA account. If you, or your vendor, know this account you will be able to log into SQL again and create new accounts that have access to the SQL system as needed.

When you create the new accounts, make them Domain User accounts, then if needed give those accounts local admin rights on that server.

scottalanmiller

@IT-ADMIN said:

really i'm lost with this now, i will wait the support guy tomorrow to see how we can set this up,
anyway i will be blamed for this, because i do it without any approval from the management because i never thought that this would cause a problem,

I've noticed that you are very silent on restoring from backups. Is this server not critical enough to be backed up?

IT-ADMIN

i'm sure if i speak with the management about this, they will said to me no since everything is OK why are you looking for trouble,,,for this reason i act by myself and do it without telling them anything
my intention was only to have a backup DC but things goes wrong out of my expectation

PSX_Defector

@IT-ADMIN said:

i'm sure if i speak with the management about this, they will said to me no since everything is OK why are you looking for trouble,,,for this reason i act by myself and do it without telling them anything

Why does this sound so familiar?

Nah, it's just in my head.

IT-ADMIN

actually this server application is very important but we don't backup the system image since it is a physical server , we just backup SQL databases

Dashrender

@IT-ADMIN said:

actually this server application is very important but we don't backup the system image since it is a physical server , we just backup SQL databases

Any reason it's not virtualized?

IT-ADMIN

@Dashrender said:

Any reason it's not virtualized?

it is another story, i'm still afraid of this transition especially the P2V step, it is scary

PSX_Defector

@IT-ADMIN said:

actually this server application is very important but we don't backup the system image since it is a physical server , we just backup SQL databases

That's all I ever do, so don't worry.

Are you just having problems RUNNING SQL or is SQL running but you can't get anyone to access it? You should be able to fire it up by giving it a new account, but the systems that connect to it will need to know the new account. That might require lots of netstat searching.

If no one can get access, you will need to do the same thing as above, but by getting into SQL and setting up user accounts. This will require the SA account or dropping into single user mode and jackin' with the info:

https://msdn.microsoft.com/en-us/library/ms188236(v=sql.105).aspx

scottalanmiller

@IT-ADMIN said:

i'm sure if i speak with the management about this, they will said to me no since everything is OK why are you looking for trouble,,,for this reason i act by myself and do it without telling them anything
my intention was only to have a backup DC but things goes wrong out of my expectation

Well in this case I'm afraid that they are correct. You did something without authorization that violates best practices somewhat significantly. It's not surprising that it caused problems. Might be best to own up to the mistake, own it and figure out a path forward rather than hiding. It is what it is, people make mistakes. You need to learn from this. Some things that appear to be issues here are:

Skipped authorization because you misunderstood the scope of your project.
Taking risk upon yourself, was there actually a reason to do this or you just wanted to? I'm unclear what prompted you to take on such a major change at all?
Ask the community, as well as management, before making a change like this. Likely we could have headed this off.
Breaking best practices is never trivial, BPs exist to protect you. There are plenty of times that you need to do something different than standard or best practice, but when doing so it not the time to assume everything is trivial and will "just work."
Make sure your third party consultants document everything, know what they are doing and that you have full access to the systems.
Virtualize every system, the more important the system, the more important it is to virtualize.
Snapshot systems before making changes, even little ones. There are tools to protect you.
Take backups. If a system is worth paying to have running, it is worth being backed up.