0

We are using SQLLocalDB to connect to a local database. No network access is required - C# Client application is on the same machine as SQLLocalDB. Sometimes I get the following error (only seems to happen on some PCs):

Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server (localdb)\myserverinstancename. ---> System.Data.SqlClient.SqlException: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=27888; handshake=0; ---> System.ComponentModel.Win32Exception: The wait operation timed out

Oddly, it seems to happen if the PC is offline, and if connected to any sort of internet connection and retried, the issue goes away. However, in most cases the connection works fine even when there is no connection.

The closest I've found to my problem is this: Connection to SQL Server Works Sometimes

But that is discussing full SQL Server, rather than SQLLocalDB, and the solution uses "Sql Server Configuration Manager", which doens't work with SQLLocalDB.

My questions:

  • Does SQLLocalDb use IPv6/TCP for some reason, even when on the same PC?
  • Is there a way to change these settings for SQLLocalDB?
  • Or is there a different cause for this issue?

Edit: With help from some of the comments below I realised that the line that fails is when it calls a database creation script. The initial attempt at connection seems to work, but when I run my creation script it throws a "Failed to connect to server".

/// This line works
var server = new Server("(localdb)\myserverinstancename");

/// this line works 99% of the time, but sometimes "failed to connect to server"
server.ConnectionContext.ExecuteNonQuery(databaseCreationScript);
Sugrue
  • 3,629
  • 5
  • 35
  • 53
  • You need to have the SQL Server Service running to be able to connect. Type Services into the start menu, and look to see what services are stopped/running beginning with SQL. – Rodney Ellis Sep 16 '20 at 11:05
  • Why are you using LocalDb instead of attaching mdf file to a server? Yes the connection to the database uses TCP. You should never use LocalDb when the mdf file is attached to the server. LocalDb is only meant for single users when you have a small database and you do not want to install the SQL Server on machine (use the Net Library to make the connection). SQL Server is designed for multi-users and will run quicker and work with larger database. So remove AttachDB from your connection string and just use Server. – jdweng Sep 16 '20 at 11:09
  • @Rodney Ellis : If you are using LocalDb the the mdf file is not on a sql server so your response doesn't make a lot of sense. – jdweng Sep 16 '20 at 11:10
  • @jdweng "LocalDb is only meant for single users when you have a small database and you do not want to install the SQL Server on machine" - yes, this is the reason I am using it. – Sugrue Sep 16 '20 at 11:17
  • @RodneyEllis I am not using SQL Server Service. I am using SQL Local DB – Sugrue Sep 16 '20 at 11:19
  • You are login into the database so you Network Password Server is used to verify the login. If the Password Server is not available you will get the error you are seeing. I think youcan prevent the error by using the path name of the mdf file instead of (localDb)\ – jdweng Sep 16 '20 at 11:27
  • @jdweng There is no password on the database. This is an error that only happens sometimes. 99% of the time it connects and works fine. – Sugrue Sep 16 '20 at 11:41
  • Read the error message again!!! You are on Windows so when a Network connection is made credentials are checked. So using (localDb) you are making a Network Connection. If you use a path like .\..\ or c:\ you are not making a network connection. – jdweng Sep 16 '20 at 11:57
  • @jdweng The mdf file is on a local hard disk. It's addressed with an absolute path, e.g. c:\database\mydatabase.mdf - Are you sure I am making a network connection? There is no network available when this issue happens (no ethernet/wifi/hardware disconnected). – Sugrue Sep 16 '20 at 12:01
  • The error says "Failed to connect to server (localdb)\Kinesense". Need to look at connection string to determine why error is occurring. The error is coming from the Driver. the error is coming from the library System.Data.SqlClient. Are you using Windows or Core? – jdweng Sep 16 '20 at 12:22
  • @jdweng Connection string is (localdb)\myserverinstancename - Again, this works 99% of the time just fine. It even works on most PCs when there is no network connection. – Sugrue Sep 16 '20 at 12:26
  • Then why did you say " It's addressed with an absolute path, e.g. c:\database\mydatabase.mdf "? You are now saying "Connection string is (localdb)\myserverinstancename". – jdweng Sep 16 '20 at 12:34
  • @jdweng I am connecting to the server instance using server = new Server("(localdb)\myserverinstancename"); and then either opening a database with server.AttachDatabase(...) or creating a new one with server.ConnectionContext.ExecuteNonQuery(databaseCreationScript); I can now see that it is the final part that fails. I will edit the question to make this clear. Thanks. – Sugrue Sep 16 '20 at 13:44
  • Then use AttchDbFilename instead of Server so you are not trying to make a connection. Never use both. The connection string webpage is wrong. See : https://www.connectionstrings.com/sql-server/ – jdweng Sep 16 '20 at 13:57
  • What should I use to create a new database? – Sugrue Sep 16 '20 at 14:01

0 Answers0