Geek Answers Handbook

.NET does not detect SQL Server instances

I have been developing a C # / SQL Server application on a Windows 8.1 device for some time using .NET 4.5.1 and SQL Server 2012 Express. All this worked flawlessly. Visual Studio 2015 was recently released and I decided to upgrade it. I also decided to upgrade my SQL Server 2012, so I fully supported it, uninstalled and installed SQL Server 2014 Express. After setting everything up and restoring the database backups, everything still worked locally, since the database connection data was still present in the settings of the saved applications.

When I tested the application on another device, I found that it could no longer connect to the instance of SQL Server and could not detect it either using

System.Data.DataTable instances = SqlDataSourceEnumerator.Instance.GetDataSources();

The application also could not detect an instance of SQL Server on my development machine, which, in my opinion, is rather strange, since it works locally. However, some other devices could detect SQL Server, so I don’t think it is a firewall related program. The SQL engine service and browser services were active.

I decided to switch to Windows 10, reset (clean installation, do not install any SQL Server programs) and run my application again, since there are other computers on my network with SQL Server, hoping this will detect these, This is not the case. The GetDataSources() method returns an empty DataTable for a second, while other devices take a few seconds to detect instances and return the correct list.

Even dumber is that the ODBC Data Source Administrator detects these network instances, making me think it is a .NET-related program.

Summary: two devices do not detect instances of SQL Server (even local ones), but other devices on the network.

+6
source share
2 answers

I decided to go with this ODBC implementation , as it works correctly, and an async / wait update can be implemented so that it does not block the user interface.

Before choosing this implementation, I also tried using another project using the SqlDataSourceEnumerator class, which worked fine. The Target framework property was set in the .NET Framework 2.0. I found that SqlDataSourceEnumerator gives the expected results (on my machine) when the target version of .NET is 3.5 or lower. Something in the .NET implementation probably changed over time, which led to the cessation of work in some specific conditions.

Edit: on my machine. I installed SQL Server 2012 again, and everything (except detection will use ODBC) works fine again. I want to thank everyone for their contribution.

+3
source

In addition to this, we had a similar problem after installing VS2015 / .Net 4.6. Our developers use SQL Express 2012 x64 (in most cases, the default instance or named instance, but the default for Express is the named instance SQLEXPRESS) and an alias to remove any machine name from the connection strings (the products use DNS records), Aliases were configured to use TCP / IP, and the server name was localhost \ instance.

But 4.6 nerfed using aliases and entity infrastructure could not connect, and SSMS could not use when using an alias. If I uninstalled 4.6 and repaired 4.5.2, which seemed to be corrupted by installing 4.6, then we could use an alias in SSMS and our application, if we had been using VS2013 since 2015, requiring 4.6. It rather denied the point of an update attempt.

I do not know exactly what caused the problem, but I know that using the fully qualified domain name of the computer that you use in the alias operation. Obtaining an instance port number and explicitly defining it in an alias also solves the problem, but if you use dynamic port allocation (and you do this by default), it will break very quickly. Alternatively, if you have one standard installation of the full version of SQL Server, it seems to continue to work fine if the alias is simply set to localhost , (local) or . .

My best guess is that some changes in .Net 4.6 somehow affect the SQL Browser service, as this should take care of routing requests to the correct instance if they use a dynamic port. Of course, it can be argued that you should always set a static port number explicitly, but who does it, of course? I played with raising it to Connect just in case, it was some kind of mistake, but I'm not sure.

EDIT

There was a connection problem because I think it really affects the SQL browser.

https://connect.microsoft.com/VisualStudio/feedback/details/1719418

+2
source

More articles:


All Articles