0

I am querying a SQL Server database with the following C# code:

SqlConnectionStringBuilder sqlCSB = new SqlConnectionStringBuilder();
sqlCSB["Data Source"] = @"SERVER\MSSQL";
sqlCSB.Remove("User ID");
sqlCSB.Remove("Password");
sqlCSB["integrated Security"] = true;
sqlCSB["Initial Catalog"] = "InitCatalog";

using (SqlConnection connection = new SqlConnection(sqlCSB.ConnectionString))
{
    using (SqlCommand cmd = connection.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM public.v_Data;";
        MessageBox.Show(System.Security.Principal.WindowsIdentity.GetCurrent().Name);
        // this shows the correct (same) user regardless from which path (remote or local) the App is running from
        connection.Open();

        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            int ord = reader.GetOrdinal("ColumnA");

            while (reader.Read())
            {
               returnedData.Add(reader.GetString(ord).Substring(2));
            }
         }
      }
   }

   return returnedData;
}

When the app is started from a local path (e.g. C:\myapp.exe), everything works just fine, but when I try to run it from e.g. Netdrive (\\\filer) (Z:), I get the following error:

System.Data.SqlClient.SqlException (0x80131904): (provider: SQL Network Interfaces, error: 26)

at 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)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Main.refresh() in Main.cs:Line 397. // connection.Open();

ClientConnectionId:00000000-0000-0000-0000-000000000000 (Error Number):-1,(State):0,(Class):20

What's the root cause? I'm not sure if some interaction between "integrated security" and starting the app from a network directory is causing the error.

As stated above it's always the same Windows user and the account has access to the network drive.

What's the best approach for debugging here?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
BC5k4KwjAnebdrfD
  • 5
  • 2
  • The following may be helpful: https://stackoverflow.com/questions/71198996/cant-access-my-sql-server-via-c-sharp-form-application-from-client-device/71199793#71199793 – Tu deschizi eu inchid Jul 01 '22 at 20:38
  • Not sure if it matters or not, but it looks like you have a lower-case `i` for `Integrated Security`. – Tu deschizi eu inchid Jul 01 '22 at 20:42
  • 1
    Are you running it directly from the UNC share path, or from a mapped network drive? If the former then perhaps it's not finding its .config file which could be introducing a multitude of issues. – AlwaysLearning Jul 02 '22 at 00:25
  • @user9938 thanks for the reply but there is no issue on the server side because the query / connection works if the app is started after it's copied to a local path – BC5k4KwjAnebdrfD Jul 04 '22 at 09:42
  • @user9938 thanks for the reply but the code works as stated in my other reply – BC5k4KwjAnebdrfD Jul 04 '22 at 09:44
  • @AlwaysLearning thanks for the reply, it was a mapped drive but i tested it again with the full UNC path and i get the same error – BC5k4KwjAnebdrfD Jul 04 '22 at 09:44

1 Answers1

0

The answer to my question can be found here: SqlConnection Error if EXE is executed from network path

"Finally I found the problem: in the server with the shared folder, SMBv2 is disabled (I don't know why) so only SMBv1 is active; the same program executed from the same client in the same network but located on a server with SMBv2 enabled works fine. So the problem is about SMBv1 share, deprecated starting from Windows 10 1803"

BC5k4KwjAnebdrfD
  • 5
  • 2