Unable to connect ADS with local SQL Server 2017 Developer Edition.

[MoreporkGithub]

Hi!
I tried connecting to a local instance of SQL server 2017 Express and received the normal Named Pipes error 40 where it cannot find the instance. After trying various combinations of Instance and server group names it finally found the instance but rejected the login, when using SQL Authentication.

I read some more and found the pre-requisites which stated that the Developer Edition was required so I upgraded SQL Server 2017 Express to the developer Edition and also replaced SSMS to version 18.

It makes no difference.

It will connect using Windows Authentication but only shows system DBs. I cannot see the actual DB I wish to use with ADS. Every SQL Authentication with every valid login and password is simply rejected with: "Login failed for user " No error codes, no further information, no clues...

I have now spent around 6 hours on this and am at the point of giving up. I decided to make this post just in case others are experiencing the same problems.

1. SSMS connects to the same instance using various SQL Authentication logins, without problem.
2. SSMS connects to the same instance using Windows Authentication, without problem. (And shows ALL DBs)
3. I have ODBC32 connections that all work perfectly both from applications and when tested in the ODBC32 creation tool. Some of them address the same target DB, on the same instance. As noted above, ADS will only connect using Windows Authentication and it does not show User DBs...
4. I have some C# applications I wrote which connect without problem to the target database, using SQL authentication in a standard connection string. ADS provides NO facility to let the user provide a connection string, neither does it allow options in the type of connection. It uses named pipes and that's the end of it. If I left the boxes for Database and Server Group set to it gave a message saying that the login succeeded but there was "nothing on the end of the pipe." Setting the database name and selecting the correct server group from the drop-down, just caused the "login failed" message noted above. I have no idea what this error message means as I don't normally use named pipes.
5. I have checked that the instance has remote connections enabled.
6. I have checked that all types of connection are enabled, with TCP/IP using default port 1433.
7. I have upgraded my software to meet the stated requirements and I STILL cannot connect properly to ADS.
8. I have checked that all SQL services are running.

If anyone has some suggestions I'd be pleased to hear them. I'd really like to experiment with this software with a view to using it for some quick DB reporting and graphing but after hours of trying I have nothing to show for it and I have to consider moving on.

Responses appreciated.

[kburtram]

@MoreporkGithub if you can connect using SSMS then the connection should also work from ADS. They both use the same SqlClient driver.

Are you using something like:

Server = localhost
Authentication Type = Sql Login
Username & password = your username

Also, do you now have two instances of SQL Server running on your localhost (for Express and Developer). If so, you may need to append the correct instance name into the server field.

[MoreporkGithub]

Thanks for your very sensible response.

The server = OPTIMUS1 (This is also the local machine name.)
Authentication type = SQL login
Username and password =

As noted, it works fine with SSMS.

I don't have any instances running on localhost. (At least as far as I know.) I certainly never set any up. The previous copy of SQL Express was updated in situ to be developer edition. (I also installed SSMS version 18 as recommended. This, in turn, detected that ADS was already installed and asked me to close it which I did. All installs terminated successfully and the machine has since been rebooted. Everything works with the new SQL Server and SSMS (except ADS). SSMS 18 automatically comes with ADS and it installs side-by-side with any existing version. I therefore uninstalled the old version. (I'm trying to give it all the help I can... :-))

I take your point about the instance name, but there is no SQL Server 2017 Express instance. There is one instance on the machine and it is OPTIMUS1. I just checked with SSMS to see if there are other engines recognized...

As you can see, it is showing a second server OPTIMUS1/OPTIMUSSQL.

It could be that I have been specifying the wrong DB server... that would account for logins being rejected... :-)

I just tried ADS again and it worked perfectly!

THANK YOU SO MUCH!

Your post made me look at it again in a different way and that's exactly what was needed.

Now I can get on with using ADS...

Cheers!