1

I just built a server (SQL 2017) and I can connect to Analysis Services and Integration Services via SSMS without issue using Windows Authentication.

When I try to connect to the Database Engine I get:

Cannot connect to myserver01.

Login failed for use 'me'. (Microsoft SQL Server, Error: 18456)

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

I checked SQL Configuration Manager and everything seems to be running properly. I restarted everything just to be safe, but no changes. I see some answers on here referring to turning on Mixed Mode, though I think that wouldn't be relevant to me as I'm using Windows Authentication.

Improve this question
3
  • If you can get to the SQL Server error log (or Windows Event log), it should give you more details on why the login failed
    – Gareth Lyons
    Feb 23 '18 at 18:16
  • Have you tried to connect locally as well as remotely? Is TCP/IP enabled in SQL Configuration Manager? Is Windows Firewall permitting inbound traffic on port 1433?
    – nkdbajoe
    Feb 23 '18 at 20:11
  • @nkdbajoe This is locally that I'm trying. I just tried to connect remotely for fun. I got "Access is denied." TCP/IP is enabled, and there is nothing in the firewall for port 1433.
    – coinbird
    Feb 23 '18 at 20:43
0

Creating SQL Server logins is a bit tricky and you need practice to understand what's going on.

There is an excellent article about it here.

In essence you need to consider the "server credentials" as well as the "database credentials". They are both different things.

I would normally run SSMS and go down to the bottom of my server instance where it's showing the Security folder (as explained in the above link). In there, expand it and right click to create a new Login. Set up your LoginID and Passwords, and default the login to the database you're referring to. Next go to Securables, and choose the correct database, along with the Schema, etc.

Once that's done you'll notice the same login was created at the database's Security folder. Make sure you've given the new Login, all the right permissions to READ and/or WRITE to the database.

The reason you need to do what I've mentioned above, is so you have a correct and valid Login to the database, which is important. Creating a single Login directly inside the database's Security is not enough.

Then you can connect to the database from external sources (Excel, programs, etc) in many ways, using the right connection strings.

Improve this answer
4
  • That would be nice, but I can't connect to the database engine to do anything in the Security folder.
    – coinbird
    Feb 27 '18 at 14:39
  • @coinbird: are you the DBA of that database? If not, you need someone with SysAdmin rights to go in and fix your login. Have you tried to login using Windows Authentication instead?
    – Fandango68
    Feb 27 '18 at 23:08
  • 1
    @coinbird: ok so you have done WinAuth. Can you access the Security folder of the server, not the database? We need more info. My answer was meant to help with proper login management.
    – Fandango68
    Feb 27 '18 at 23:10
  • 1
    Choosing this as the correct answer. The login was wrong. I had to use a process to manually change the sysadmin account, found here: sqlserver-help.com/2012/02/08/…
    – coinbird
    Feb 28 '18 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.