I am building an Windows Forms App an can not connect to my SQL Server from within Visual Studio. If I build the app and start it separately from the folder, connection is instantly established and communication with the Server works. Then I can read/write/delete successfully.
If I start/run the application from within Visual Studio I cannot talk with the SQL Server. In the debugger output, I see an exception:
'System.Data.SqlClient.SqlException' in System.Data.SqlClient.dll
After the connection timeout I get an error.
...(provider: TCP, error: 0 - on Database call an not resolvable error occurred) [my translation from German]
The server listens on a non standard Port (30500), in the meantime I switched to 1433 and back and nothing is working, from within the IDE.
I use:
Microsoft Visual Studio Community 2022 (x64) with non-admin domain account, v17.8.5
Microsoft .NET Framework Version 4.8.04084
Visual C++ 2022 00482-90000-00000-AA825
Microsoft Visual C++ 2022
ASP.NET and Web Tools v17.8.358.6298
Azure App Service-Tools v3.0.0
C#-Tools v4.8.0-7.23572.1+7b75981cf3bd520b86ec4ed00ec156c8bc48e4eb
Common Azure Tools v1.10
Microsoft JVM Debugger v1.0
NuGet Paket Manager v6.8.0
SQL Server Data Tools v17.8.120.1
Microsoft SQL Server Data Tools
SQLite & SQL Server Compact Toolbox 4.8
TypeScript tools v17.0.20920.2001
TypeScript tools for Microsoft Visual Studio
Visual Basic tools v4.8.0-7.23572.1+7b75981cf3bd520b86ec4ed00ec156c8bc48e4eb
Microsoft Visual F# Tools
Visual Studio IntelliCode v2.2
KI-based development for Visual Studio
My code is essentially just creating the connection string, placing it into the connection object and (trying to) open connection.
Public Const strDBSource As String = "Data Source=Computername,30501\SQLEXPRESS;Initial Catalog=xxx_DB;Persist Security Info=True;User ID=xxx;Password=xxx;Encrypt=False;TrustServerCertificate=True"
''alternative
Private connectionString_FuEDB As String = New SqlConnectionStringBuilder With {.DataSource = "TTS-NB-LABOR,30501", '"192.168.13.62,30501\SQLEXPRESS",
.InitialCatalog = "FXXxxx_DB",
.UserID = "xxx",
.Password = "xxx",
.TrustServerCertificate = True}.ConnectionString
[...]
sqlCon = New SqlClient.SqlConnection
sqlCon.ConnectionString = strDBSource
'sqlCon.ConnectionString = connectionString_FuEDB
[...]
Try
If sqlCon.State <> ConnectionState.Open Then
Debug.WriteLine("State: not (jet) Open, trying to open..")
sqlCon.Open()
[...]
End Try`
As the connection works with the (release) build, I assume there isn't something essential wrong from the code point of view.
I can connect from SQL Server Mgmt Studio, I can connect via an .udl test connection. I can connect via the VS integrated SQL Server Explorer. I can not connect from my app.
I tried various connection strings at first. They all failed. After some time I tried to connect to an other SQL Server where I have the connection string and also an working method, to try to establishing an connection. This also failed. Then I build the damn thing and started it from explorer and connection succeeded!
I tried named pipes, but same error, also I did not invest to much time into it. It may had connection string issues as well.
I made sure all ports are open on the server side. As connection is working from the build, I think it works.
Somewhere on the way I made an new application just for connection-testing. Doesn't work from the IDE (in Debug mode). From this application I tested a lot of variations of connection strings, most of them worked. Some where just plain wrong. Non of them work from within the IDE.
I came across an MS Article which stated that port 135 TCP should be open for Visual Studio and SQL Server Management Studio, the are open. In fact for testing I opened all ports for those two applications.
Any ideas what else I can try?
Update
I saw I can start the program without debugging. Then it is working. I edited the debug mode. Enabled and disabled Hot Reload and SQL Server debugging. Same result, not working in debug mode.
2nd Update
As proposed by @HardCode I post the whole connection Section from my code:
Imports System.ComponentModel
Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.Windows.Forms.VisualStyles.VisualStyleElement
Imports Microsoft.IdentityModel.Tokens
Public Class Hauptfenster
[..some other variable declarations.]
Public Const strDBSource As String = "Data Source =Name-OfDevice-LABOR,30xxx;Database=xxx_DB;User ID=SA;Password=sa;"
'Public Const strDBSource As String = "Data Source=Name-OfDevice-LABOR,30xxx;Initial Catalog=xxx_DB;Persist Security Info=True;User ID=SA;Password=sa;Encrypt=False;TrustServerCertificate=True"
Private connectionString_xxxDB As String = New SqlConnectionStringBuilder With {.DataSource = "Name-OfDevice-LABOR,30xxx1", '"192.168.local.ip,30xxx",
.InitialCatalog = "xxx_DB",
.UserID = "SA",
.Password = "sa",
.TrustServerCertificate = True}.ConnectionString
Dim sqlCon As SqlConnection
Dim sqlQuerry As SqlCommand
Private Sub openConnection()
If IsNothing(sqlCon) Then
sqlCon = New SqlClient.SqlConnection
sqlCon.ConnectionString = strDBSource
'sqlCon.ConnectionString = connectionString_xxxDB
End If
Ausgabe_Log(String.Format("ConnectionString: {0}", sqlCon.ConnectionString))
Ausgabe_Log(String.Format("State_: {0}", sqlCon.State))
Try
If sqlCon.State <> ConnectionState.Open Then
Debug.WriteLine("State: not (jet) Open, trying to open")
sqlCon.Open()
Ausgabe_Log(String.Format("Resultat nach Verbindugnsversuch: {0}", sqlCon.State))
End If
Catch sqlexception As SqlClient.SqlException
MsgBox(sqlexception.Message)
Ausgabe_Log(String.Format("SQL-Fehler: {0}", sqlexception.Message))
Debug.WriteLine(sqlexception.Message)
Catch ex As Exception
MsgBox(ex.Message)
Ausgabe_Log(String.Format("Allg. Fehler: {0}", ex.Message))
Debug.WriteLine(ex.Message)
End Try
The exception is happening at :
sqlCon.Open()
I tried to trace the source of the Error and am currently stuck somewhere in DbConnectionPools.cs in TryGetConnection(...).
There is an condition check dbConnectionInternal == null. It enters this path.
This line of code fails:
dbConnectionInternal = UserCreateRequest(owningObject, userOptions);
I am getting an Win32Exception on a Win 10 Pro - x64 System. This is wondering me a little bit. The SQL Server is running on a x32-System so this is maybe related? I just caught my eye and I just want to mention it.
Update 3 (2024-02-12)
I Implemented the whole communication with EF Core. I still can not get any SQL connection in debug mode.
I recreated the whole project in vb on .NET 7 from a clean project file. Just copied the, the few files with written code. And installed all packages for .NET 7 again. Same result.
Today I relocated the Project to a local Drive. Away from "User-Networkdrive" which is the default Userfolder, as we get it assigned per Domain-Settings.
I started to get perfomance Problems and was wondering why. Recognized that the Data are not localy stored. After moving Project to local drive I thought why not try Debuging again. It worked.
I still have ocasionaly some timing issues which result in Exceptions. But the essential Problem of not getting an Connection in Debug-Mode is resolved.