Ошибка 18470 sql server

Stuck with Microsoft SQL server error: 18470? We can 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 Bobcares we often get requests from our customers to fix similar issues as a part of our Server Management Services.

Today, let’s see how hour Support Engineers fix this SQL server error for our customers.

How to fix Microsoft SQL server error: 18470?

A typical error message which occurs when we try to connect with the SQL server after the SA user account in the SQL server is disabled is shown below:

Microsoft SQL server error: 18470

We will see the steps followed by our Support Engineers to fix Microsoft SQL server error: 18470  for our customers.

1. Enable the SA user account

Steps for enabling the SA user account is given below:

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

2. Then go to SSMS and take Server

3. From there take Security and go to Logins.

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

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

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

Microsoft SQL server error: 18470

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

8. 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:

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

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

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

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

Microsoft SQL server error: 18470

5. And click the OK button.

6. 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.]

Conclusion

To conclude, we saw what causes SQL server error: 18470, also we saw the steps which our Support Engineers follow to fix this error.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник
  • Remove From My Forums

  • Вопрос

  • Добрый вечер!Не могу решить проблему.

    Проблема в том,что не могу подключиться к MS SQL,выдаёт ошибку 18470.

    Помогите решить проблему!!Не могу из-за этого закончить диплом ((

Ответы

  • если посмотреть текст ошибки, то

    select * from sys.messages
    where message_id=18470

     Пользователю «%1!» не удалось войти в систему. Причина: учетная запись отключена.%2!

    говорит о том, что ваша УЗ отключена (disabled)

    Войдите под другой УЗ и «включите» её если авторизация СКЛ, либо попросите вашу УЗ активизировать в АД (если Вин-авторизация)

    http://www.t-sql.ru

    • Предложено в качестве ответа

      27 мая 2010 г. 7:45

    • Помечено в качестве ответа
      Daniil KhabarovModerator
      8 июня 2010 г. 11:02

Источник

RRS feed

  • Remove From My Forums

  • Question

  • Hi

    I installed I had connected to the database. I went as disabled login and now I cant connect. All I get is error message 18470. How can I get this to connect?

    thanks

    Jack

Answers

  • Hopefully you have a SA account or you another sysadmin level account that you can enable this account.  If not you may need to rebuild master.

All replies

  • Hopefully you have a SA account or you another sysadmin level account that you can enable this account.  If not you may need to rebuild master.

Источник

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.  ]​

Источник

  • 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's user avatar

oNare

3,0922 gold badges19 silver badges35 bronze badges

asked Aug 11, 2015 at 13:53

hoF's user avatar

3

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

Hector's user avatar

HectorHector

1,0121 gold badge8 silver badges22 bronze badges

7

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

Rajiv Rajan's user avatar

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

Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка 1827 ниссан патфайндер r51 дизель
  • Ошибка 18253 vag
  • Ошибка 18456 серьезность 14 состояние 8 sql server
  • Ошибка 18456 sql server при подключении
  • Ошибка 1825 msg