SA Password Unknown

Question

SA Password Unknown

2Tall

SSChampion

Points: 14632
More actions
August 16, 2017 at 3:01 am

#344076

Hi to all. SQL 2016 was installed in mixed mode. I cannot recall specifying SA password for a particular instance when I installed an application DB. I have access to the default instance as I specified accounts on install.
I have tried to add a domain user account to the sysadmin role (EXEC sp_addsrvrolemember 'DOMAIN\user', 'sysadmin';).
The instance is SQL Server (UPC). Steps taken.
1) Placed instance in single user mode by adding parameter -m
2) sqlcmd -S .\UPC
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user '
DOMAIN\user'. Reason: Server is in single user mode. Only one administrator can connect at this time.
I need to reset SA password or add an account to the sysadmin role.
Any help appreciated.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 15 posts - 1 through 15 (of 24 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If you specified accounts at install, they'll be sysadmin. Log in as one of those and add extra accounts or change the sa password.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

2Tall SSChampion Points: 14632 More actions · Answer 2

Hi. Unfortunately the accounts I added are on the default instance, not the named instance!
Latest effort:
C:\Windows\system32>sqlcmd /E /S SERVER\UPC
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user '
Domain\user'. Reason: Server is in single user mode. Only one administrator can connect at this time..

Kind Regards,
Phil

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

When the instance is in single user mode, the only accounts that can log on are SQL sysadmins or windows users who are members of the local administrators group. If you have no accessible sql sysadmin logins, then use a windows login that is a member of the local administrator group.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

2Tall SSChampion Points: 14632 More actions · Answer 4

Hi. Yes I was aware of that. The account I am logged into the server with is in the local administrators group. Can you confirm the sqlcmd I should use to add that same account to the sysadmins role for instance named UPC.

Thanks,
Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Once you connect via SQLCMD, you can do anything. You're a sysadmin. So the same T-SQL you'd use normally to create a login and make it sysadmin.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Also make sure that you don't have something like SQL Agent or other services attempting to log in and taking the only login available.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

2Tall SSChampion Points: 14632 More actions · Answer 7

Hi. I must be missing a trick, my issue is connecting using my admin account.

C:\Windows\system32>sqlcmd -S SERVER_NAME\UPC -U 'Domain\MyAccount'
Password: Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user ''Domain\MyAccount''..

Thanks,
Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Have a look at the error log, see what the detailed description is for the login failure is.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

2Tall SSChampion Points: 14632 More actions · Answer 9

Hi. The application log reports an informational message 'Login failed for user 'user'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

Thanks,
Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

-U on sqlcmd is used along with -P for SQL logins, not windows. Use the parameter for trusted connection and make sure that you're logged into Windows as a local admin.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

2Tall SSChampion Points: 14632 More actions · Answer 11

Hi. What is the correct syntax to use sqlcmd -E to connect to instance UPC? I am now logged in using a local admin account and the server is in single user mode.

C:\Windows\system32>sqlcmd -E
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user '
GBLU1SVAPP01\MyLocalAdminAccount'..
Thanks,
Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

sqlcmd /?
and look at the options it presents you.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

2Tall - Wednesday, August 16, 2017 5:10 AM
Hi. What is the correct syntax to use sqlcmd -E to connect to instance UPC? I am now logged in using a local admin account and the server is in single user mode.
C:\Windows\system32>sqlcmd -E
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user '
GBLU1SVAPP01\MyLocalAdminAccount'..
Thanks,
Phil.

Possibly not the syntax that needs changing. If the serve is in single user mode, you won't be able to connect if someone or something got there before you. Have a look at the errorlog (open the text file directly) to see what the reason for the login failure was.

John

2Tall SSChampion Points: 14632 More actions · Answer 14

Hi. I have taken a look at the commands. I thought I was attempting the correct syntax but I keep hitting a brick wall.

I am currently logged in using a local admin account. I have placed the database in single user mode. From the command line I entered sqlcmd -E

ODBC Driver 13 for SQL Server ; Login failed for user SERVER\User.
Thanks,
Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper