Microsoft sql server ошибка 18470

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

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

Источник

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

Источник

SQL Server has two types of authentication namely,

Windows authentication
Mixed mode (Windows and SQL Server authentication).

If the Mixed mode authentication is not enabled on the server, there will be some issues.

could not connect using SQL Server Authentication
The ‘sa’ account will be disabled.

For security reasons, the ‘sa’ account is usually disabled, as it is a well-known SQL server account and it’s often targeted by malicious users. And so, be thoughtful while enabling the ‘sa’ account. And it’s recommended that you use a strong password.

Enable mixed-mode authentication:

Login into the server using Windows authentication.
Right-click your server and go to Properties, then select Security.
Under the Server Authentication section, you will find that the “Windows Authentication Mode” radio button is selected.
So, your work is to select the “SQL Server and Windows Authentication Mode” radio button.
Then click Ok.
Right-click the server again and select “Restart”
That is it! You have now enabled mixed-mode authentication on your server.

Enabling the disabled account.

Login using windows authentication.
Go to Security, then log in. You will find the disabled account with the small red arrow beside them
Right-click the login name and go to Properties.
You should see something like this with the “Disabled” radio button checked.

SQL Server login failed for user. Reason: The account is disabled (Microsoft SQL Server, Error: 18470)
You need to check the Enabled radio button like this

SQL Server login failed for user. Reason: The account is disabled (Microsoft SQL Server, Error: 18470)
Restart the server.
Another easier way of enabling ‘sa’ account and set a password using a query as shown below.

ALTER LOGIN sa ENABLE;

ALTER LOGIN sa WITH PASSWORD = ‘you new strong password’;

That’s it! You have Mixed mode authentication and account enabled.

Have questions? Contact the technology experts at InApp to learn more.

Источник

Over the time, I have been using Windows Authetication to connect to my MSSMS (Microsoft Sql Server Management Studio). And since during installation I had not enabled Mixed mode authetication on the server, I got some issues.

I could not connect using Sql Server Authetication
The ‘sa’ account was disabled.

For security reasons, the ‘sa’ account is usually disabled, as it is a well known SQL server account and its often targeted by malicious users. And so, be thoughful while enabling the ‘sa’ account. And its recommended that you use a strong password.

I thought to summarize the short steps I gathered on the internet into one post. So let’s go.

Enable mixed mode authetication:

Login in to the server using the Windows authetication.
Right click your server and go to Properties, the select the Security
Under Server Authetication section, You will find that “Windows Authetication Mode” radio button selected.
So, your work is to select “Sql Server and Windows Authetication Mode” radio button

server_properties_to_enable_mix_mode_authetication

Then click Ok
Right click the server again and select “Restart“
That is it! You have now enabled mixed mode authetication on you server.

Enabling the disabled account.

Login using windows authetication
Go to Security, then Login. You will find the disabled account with the a small red arrow beside them as below

Disabled_account_arrow

Right click the login name and go to Properties
You should see something like this with “Disabled” radio button checked

Login_disabled_radio_button

You need to check the Enabled radio button like this

Enable_radio_button

Restart the server.

Another easier way of enabling ‘sa’ account and set a password using query as shown below.

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

Thats it! You have Mixed mode authetication and account enabled .

Happy reading!!

Tags: MSSQL, sa

Источник

Remove From My Forums

Question
I installed an accounting application that need SQL Server database, In setup wizard of accounting application all configurations set via program manufacture. After installing, all my sql users like ‘sa’ and windows built in users not log in sql server managment
studio and show this message to me «Login failed for user ‘username’. Reason……»

How can i log into sql server management when i haven’t any enabled users?

Thanks
- Edited by
  
  Thursday, September 28, 2017 9:52 PM

Источник

Remove From My Forums

Question
I installed an accounting application that need SQL Server database, In setup wizard of accounting application all configurations set via program manufacture. After installing, all my sql users like ‘sa’ and windows built in users not log in sql server managment
studio and show this message to me «Login failed for user ‘username’. Reason……»

How can i log into sql server management when i haven’t any enabled users?

Thanks
- Edited by
  
  Thursday, September 28, 2017 9:52 PM

Источник

Microsoft sql server ошибка 18470

Вопрос

Ответы

Вопрос

Все ответы

Question

Question

А вот еще интересные материалы: