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.
Best Answer
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
Securityfolder (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.