Ошибка 18470 sql server - Исправление ошибок и поиск оптимальных решений проблем

Remove From My Forums

Вопрос
I always used Windows Authentication to login SSMS.

Today it shows error 18470, user ‘SERVERAdministrator’ account is disabled.

Although I still use the sa to login, but how does it comes? and how to enable it again?

Thanks.

Все ответы

and how to enable it again?

With
```
USE [master]
GO
ALTER LOGIN [SERVERAdministrator] ENABLE;
```
Olaf Helper

[ Blog] [ Xing] [ MVP]
- Предложено в качестве ответа
  
  23 июня 2020 г. 6:41
- Отменено предложение в качестве ответа
  MIAOYUXI
  23 июня 2020 г. 6:42
Fail to enable the login SERVERAdministrator

Error: Cannot alter the login ‘SERVERAdministrator’, because it does not exist or you do not have permission.

I check the Logins as below

I create a new login SERVERAdministrator, but it shows: server principal ‘SERVERAdministrator’ already exists error 15025.

How to fix it?

does SQL being hacked? how can I check?

Thanks.
Hi UOG,

Administrator account has been disabled and other accounts do not have permission to enable it? for your case, backup your dbs and then

uninstall and
reinstall sqlserver.

Best Regards.

m

MSDN Community Support
Please remember to click «Mark as Answer» the responses that resolved your issue, and to click «Unmark as Answer» if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to
MSDN Support, feel free to contact MSDNFSF@microsoft.com
My computer name is SERVER, not DESKTOP-7BRJ60Q
Hi UOG Admin,

I think the most simple and helpful way is to uninstall and reinstall.

I have encountered this and try to use create a new windows administrator account and then try to use DAC method to create one new login and grant it admin permission, however it was fail so I I decisively choose to uninstall and then reinstall
after backup my useful dbs, or add another new instance. And this will not take much time.

Best Regards.

m

MSDN Community Support
Please remember to click «Mark as Answer» the responses that resolved your issue, and to click «Unmark as Answer» if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to
MSDN Support, feel free to contact MSDNFSF@microsoft.com
Hi UOG Admin,

Is the reply helpful?

Best Regards.

m

MSDN Community Support
Please remember to click «Mark as Answer» the responses that resolved your issue, and to click «Unmark as Answer» if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to
MSDN Support, feel free to contact MSDNFSF@microsoft.com
Hi,

Is the issue solved?

BR

MIAOYUXI

MSDN Community Support Please remember to click «Mark as Answer» the responses that resolved your issue, and to click «Unmark as Answer» if not. This can be beneficial to other community members reading this thread. If you have any
compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

I am new in SQL Server. Wrongly I disabled my instance when I’d worked with Logins. Now I can not connect to Database Engine.

Could you please help me?

oNare

3,0922 gold badges19 silver badges35 bronze badges

asked Aug 11, 2015 at 13:53

Download PSExec from Microsoft, then use it to launch Management Studio once you have it installed: PsExec -s -i «C:…Ssms.exe»

This will connect as NT AUTHORITYSYSTEM and will allow you to do things in Object Explore such as reset SA password. Thanks to user Aaron Bertrand for the information…

answered Aug 11, 2015 at 14:13

HectorHector

1,0121 gold badge8 silver badges22 bronze badges

ok looks like you have locked out your SQL login.

Hit the Windows key to bring up the Start menu along with the Search bar. Type «services.msc» in the Search bar. This will launch the Services MMC.

Now find your SQL Server and SQL Server Agent services and stop both of them. Right click the SQL Server service > Properties > Startup and add «-m» in the «Specify a startup parameter» box (without the double quotes). This will start it in single user mode. Now start the SQL Server service.

Next, bring up a command prompt and run this :

sqlcmd -S your_server_name -E

The «-E» parameter is used to start in Windows Authentication. Now use this syntax to create a new powerful account :

create login POWERFUL_USER with password=’Change@1000′
go
exec sp_addsrvrolemember [POWERFUL_USER], [sysadmin]
go
exit

This will now exit you from the command window. You can now connect to SQL Server (with SQL Authentication) by using the POWERFUL_USER username and password.

answered Aug 11, 2015 at 15:12

Источник

Are you facing Microsoft SQL server error: 18470?

This guide will help you.

The SQL Server Error : 18470 mainly occurs when the SA (System Administrator) user account is disabled in the SQL Server. Enabling SA user account helps to fix this error

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to resolve SQL Server related Error.

In this context, we shall look into steps to fix this SQL server error.

How to fix Microsoft SQL server error: 18470?

You can apply the tips given in the following steps to fix to fix Microsoft SQL server error 18470.

1. Enable the SA user account

Steps for enabling the SA user account is given below:

i. First, we will log in to SQL Server Management Studio (SSMS) using Windows Authentication.

ii. Then go to SSMS and take Server

iii. From there take Security and go to Logins.

iv. After that, right-click on the SA account and select Properties.

v. In the Login Properties screen, select Status on the left side.

vi. We will see that the Disabled option is checked.

vii. Will will change the Login option from Disabled to Enabled and then click OK.

viii. Finally we will restart the server.

An alternative and easier way for our customers to enable SA account and set a password using the query below:

ALTER LOGIN sa ENABLE;
GO
ALTER LOGIN sa WITH PASSWORD = 'you new strong password';
GO

2. Enabling mixed-mode authentication

The steps to follow are given below:

i. First, we can log in to the server using Windows authentication.

ii. Then right-click on the server and go to Properties from there take Security.

iii. Under the Server Authentication section, we will find that the “Windows Authentication Mode” radio button selected.

iv. Next, we will select the “SQL Server and Windows Authentication Mode” radio button.

v. And click the OK button.

vi. Finally we will restart the server.

This will fix the Microsoft SQL server error: 18470.

[Stuck with Microsoft SQL server error: 18470? We’ll help you. ]

Источник

I’m locked out of a local database, and the single-user mode option that has worked for me in the past isn’t working for me here. I could use some options to try.

It’s saying the account is disabled, which I didn’t think was even possible for single-user mode.

What I’ve tried already:

Restarting in single-user mode:

C:Program FilesMicrosoft SQL ServerMSSQL10_50.DBNAMEMSSQLBinn>net stop MSSQL$DBNAME
The SQL Server (DBNAME) service is stopping..
The SQL Server (DBNAME) service was stopped successfully.


C:Program FilesMicrosoft SQL ServerMSSQL10_50.DBNAMEMSSQLBinn>.sqlservr -sDBNAME -m -c

[seems to come up]

The login attempt looks like this:

C:Program FilesMicrosoft SQL ServerMSSQL10_50.DBNAMEMSSQLBinn>sqlcmd -S .DBNAME
Msg 18470, Level 14, State 1, Server MY-PCDBNAME, Line 1
Login failed for user 'My-PCsarahg'. Reason: The account is disabled.

Server output:

2012-05-29 15:55:25.11 Logon       Error: 18470, Severity: 14, State: 1.
2012-05-29 15:55:25.11 Logon       Login failed for user 'My-PCsarahg'. Reason: The account is disabled. [CLIENT: <local machine>]

Restarting in DAC mode:

C:Program FilesMicrosoft SQL ServerMSSQL10_50.DBNAMEMSSQLBinn>.sqlservr -sDBNAME -m -c -t 7806
... blah blah ...
2012-05-29 15:59:05.59 spid6s      Server name is 'MY-PCDBNAME'. This is an informational message only. No user action is required.
2012-05-29 15:59:05.60 spid9s      Starting up database 'model'.
2012-05-29 15:59:05.60 spid6s      Informational: No full-text supported languages found.
2012-05-29 15:59:05.60 spid6s      Starting up database 'msdb'.
2012-05-29 15:59:05.72 Server      A self-generated certificate was successfully loaded for encryption.
2012-05-29 15:59:05.72 Server      Server is listening on [ 'any' <ipv6> 49201].
2012-05-29 15:59:05.74 Server      Server is listening on [ 'any' <ipv4> 49201].
2012-05-29 15:59:05.74 Server      Server local connection provider is ready to accept connection on [ \.pipeSQLLocalDBNAME ].
2012-05-29 15:59:05.74 Server      Server named pipe provider is ready to accept connection on [ \.pipeMSSQL$DBNAMEsqlquery ].

The login attempt looks like this:

C:Program FilesMicrosoft SQL ServerMSSQL10_50.DBNAMEMSSQLBinn>sqlcmd -A -S .DBNAME
Msg 18470, Level 14, State 1, Server MY-PCDBNAME, Line 1
Login failed for user 'Mys-PCsarahg'. Reason: The account is disabled.

With server log:

2012-05-29 16:00:24.86 Logon       Error: 18470, Severity: 14, State: 1.
2012-05-29 16:00:24.86 Logon       Login failed for user 'Mys-PCsarahg'. Reason: The account is disabled. [CLIENT: 127.0.0.1]

Any help is very welcome,

— Sarah

Источник

I’m locked out of a local database, and the single-user mode option that has worked for me in the past isn’t working for me here. I could use some options to try.

It’s saying the account is disabled, which I didn’t think was even possible for single-user mode.

What I’ve tried already:

Restarting in single-user mode:

C:Program FilesMicrosoft SQL ServerMSSQL10_50.DBNAMEMSSQLBinn>net stop MSSQL$DBNAME
The SQL Server (DBNAME) service is stopping..
The SQL Server (DBNAME) service was stopped successfully.


C:Program FilesMicrosoft SQL ServerMSSQL10_50.DBNAMEMSSQLBinn>.sqlservr -sDBNAME -m -c

[seems to come up]

The login attempt looks like this:

C:Program FilesMicrosoft SQL ServerMSSQL10_50.DBNAMEMSSQLBinn>sqlcmd -S .DBNAME
Msg 18470, Level 14, State 1, Server MY-PCDBNAME, Line 1
Login failed for user 'My-PCsarahg'. Reason: The account is disabled.

Server output:

2012-05-29 15:55:25.11 Logon       Error: 18470, Severity: 14, State: 1.
2012-05-29 15:55:25.11 Logon       Login failed for user 'My-PCsarahg'. Reason: The account is disabled. [CLIENT: <local machine>]

Restarting in DAC mode:

C:Program FilesMicrosoft SQL ServerMSSQL10_50.DBNAMEMSSQLBinn>.sqlservr -sDBNAME -m -c -t 7806
... blah blah ...
2012-05-29 15:59:05.59 spid6s      Server name is 'MY-PCDBNAME'. This is an informational message only. No user action is required.
2012-05-29 15:59:05.60 spid9s      Starting up database 'model'.
2012-05-29 15:59:05.60 spid6s      Informational: No full-text supported languages found.
2012-05-29 15:59:05.60 spid6s      Starting up database 'msdb'.
2012-05-29 15:59:05.72 Server      A self-generated certificate was successfully loaded for encryption.
2012-05-29 15:59:05.72 Server      Server is listening on [ 'any' <ipv6> 49201].
2012-05-29 15:59:05.74 Server      Server is listening on [ 'any' <ipv4> 49201].
2012-05-29 15:59:05.74 Server      Server local connection provider is ready to accept connection on [ \.pipeSQLLocalDBNAME ].
2012-05-29 15:59:05.74 Server      Server named pipe provider is ready to accept connection on [ \.pipeMSSQL$DBNAMEsqlquery ].

The login attempt looks like this:

C:Program FilesMicrosoft SQL ServerMSSQL10_50.DBNAMEMSSQLBinn>sqlcmd -A -S .DBNAME
Msg 18470, Level 14, State 1, Server MY-PCDBNAME, Line 1
Login failed for user 'Mys-PCsarahg'. Reason: The account is disabled.

With server log:

2012-05-29 16:00:24.86 Logon       Error: 18470, Severity: 14, State: 1.
2012-05-29 16:00:24.86 Logon       Login failed for user 'Mys-PCsarahg'. Reason: The account is disabled. [CLIENT: 127.0.0.1]

Any help is very welcome,

— Sarah

Источник

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" alt="RRS feed" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif?cver=0001" />

Вопрос

Все ответы

How to fix Microsoft SQL server error: 18470?

1. Enable the SA user account

2. Enabling mixed-mode authentication

[Stuck with Microsoft SQL server error: 18470? We’ll help you. ]​

Restarting in single-user mode:

Restarting in DAC mode:

Restarting in single-user mode:

Restarting in DAC mode:

[Stuck with Microsoft SQL server error: 18470? We’ll help you. ]