How to access SQL Server named instance remotely from web service using SQL Server authentication

I know this problem has been widely discussed, but after almost two days of research, I could not find a working solution yet.

I have a SQL Server that hosts four instances of Versions 2012, 2014, 2016 and 2017, of which 2012 is the default instance. Everything works fine, as long as I connect to the default instance.

Also, it is possible to connect to each instance with Management Studio or .udl connection string generator. But I can not connect to a database in any of the named instances with a web service, where the application pool runs under the ApplicationPoolIdentity.

I have checked multiple times, that the SQL Server Browser is running, that the firewall is opened for each sqlservr.exe as well as Ports 1433 and 1434. And I think otherwise the Management Studio connection wouldn't be possible.

Still to remark is, that I can access each instance if I start the Visual Studio internal IIS Express so that it runs under my personal Windows account. This account has stored credentials for the SQL Server machine. Knowing this, I tried to add a SQL Server login for my machine, but SQL Server does not accept usernames of type .\mymachine$ or \\.\mymachine$ and I have no AD to choose from.

This behavior is reproducible on other servers as well as from simple console applications, that only try to connect to a database of a named instance.

The error message says:

Error 1326 "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)".

The inner Exception is something like "Wrong username or password" (it's in german).

Edit To make things clearer I want to give a simple example, that shows the mysterious behavior:

using System;
using System.Data.SqlClient;

namespace ConsoleConnect
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(@"User ID=user;Password=pwd;Persist Security Info=False;Connect Timeout=5;Initial Catalog=master;Data Source=mySqlServer"))
                {
                    con.Open();
                    Console.WriteLine("Default instance opened");
                    con.Close();
                }
                using (SqlConnection con = new SqlConnection(@"User ID=user;Password=pwd;Persist Security Info=False;Connect Timeout=5;Initial Catalog=master;Data Source=mySqlServer\namedinstance"))
                {
                    con.Open();
                    Console.WriteLine("Named instance opened");
                    con.Close(); 
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            Console.ReadKey();
        }
    }
}

While I have my credentials stored for OS Access of mySqlServer this outputs:

Default instance opened
Named instance opened

When I remove the stored credentials it outputs

Default instance opened
System.Data.SqlClient.SqlException (0x80131904): Netzwerkbezogener oder instanzspezifischer Fehler beim Herstellen einer Verbindung mit SQL Server. Der Server wurde nicht gefunden, oder auf ihn kann nicht zugegriffen werden. Überprüfen Sie, ob der Instanzname richtig ist und ob SQL Server Remoteverbindungen zulässt. (provider: TCP Provider, error: 0 - Der Wartevorgang wurde abgebrochen.) ---> System.ComponentModel.Win32Exception (0x80004005): Der Wartevorgang wurde abgebrochen
   bei System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   bei System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   bei System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   bei System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   bei System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   bei System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   bei System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   bei System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   bei System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   bei System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   bei System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   bei System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   bei System.Data.SqlClient.SqlConnection.Open()
   bei ConsoleConnect.Program.Main(String[] args) in F:\Tobias\Dokumente\tmp\Projects\ConsoleConnect\ConsoleConnect\Program.cs:Zeile 24.
ClientConnectionId:00000000-0000-0000-0000-000000000000
Fehlernummer (Error Number):258,Status (State):0,Klasse (Class):20

Thanks in advance, Tobias

Make sure your SQL installation accepts "SQL Server authentication". Simple test is using "Microsoft SQL Server Managemnt Studio".

if it does, then check your connection string

<add name="MycConnectionName" connectionString="Data Source=SQL01.mydomain.com\SQL;Initial Catalog=MYDBName;uid=youruser; pwd=yourpass;"
    providerName="System.Data.SqlClient" />