locked
LS First connect to SQL database fails. RRS feed

  • Question

  • Very strange problem...

    After publishing LS application (external SQL database) the first time I attempt to connect to a table in the database the read fails.  If I just try the same function again, it works and will keep working fine until I close the program and then open it again.  The first access will fail and then all functions after that work fine.

    Here is the sequence:

    Open the published LS application.

    Select Find Order... enter the order number and click the find order button.

    Program hangs for a few seconds and then displays a red X where the order should be.

    Put the order number in again and click the find order button again.  This time it brings the order up with no problem and from then on everything works as expected.

    Close the program and the re-open.

    Select Find Order... enter the order number and click the find order button.

    Program hangs for a few seconds and then displays a red X where the order should be.

    Put the order number in again, click the find order button and everything works fine again. 

    Very strange.

    Where do I start look for this one?  hahahahaha. 

         

    Friday, January 18, 2013 8:06 PM

Answers

  • I found the problem and you won't believe what it was. (Well... Google found the problem).

    Apparently when you install SQL Server Express 2008 R2 (I don't know about other versions) it creates a default instance that will listen on the default port 1433. However, if you create a new instance, it sets up a dynamic port and listens on a port assigned by SQL Express but I have no idea what criteria it uses to determine that port. Mine was port 49196 but it can be anything (within some range that I don't know).

    Inasmuch as the new named instance is not listening on the standard port, opening the standard SQL port on the firewall won't work. You can either open the port that SQL Express is listening on or you can change the port SQL Express is listening on.

    To find which port SQL is listening on or the change the port SQL is listening on, open SQL Server Configuration Manger and expand SQL server network configuration. Click on Protocols for SQL Express and then right click on TCP/IP in the right panel and select properties. Click on the IP addresses tab and then scroll all the way to the bottom and find the IP ALL section. There you will find the TCP Dynamic Port your instance of SQL Express is listening on. Either open that port on your firewall or assign a different port to the TCP Dynamic Port. (make sure the port you assign is open for connections).

    That's it. The fail on the first connection attempt problem will be gone. But don't ask me why it always worked on the second attempt before the port was assigned properly... I have no clue.

          

    • Marked as answer by Jyuma1 Thursday, January 24, 2013 5:53 PM
    Thursday, January 24, 2013 5:53 PM

All replies

  • That could be a connection timeout. It sounds like the connection pooling is working, by default the first connection to a sql server by a client will take about 30 to 50 seconds to establish then as the connection has been made and the security verified the next connections will connect a lot quicker.

    You could extend the connection timeout in the connection string for the database, you will have to edit the app.config files to do this.

    Saturday, January 19, 2013 5:15 AM
  • That sounds like it might be correct... although I'll have a tough time convincing the client that every time he clicks on the program icon and the program starts, it's normal for his first selection to take as long as 50 seconds to come to the screen. I can just hear him now... "It doesn’t do that when I use Access! I thought you told me this was more advanced technology?” Grrrr. (hehehe)

    Also... now that you mention it, when I publish the application... at the point where you enter the connection strings for the user and the admin, there is a “Test Connection" button and it too fails the connection on the first connection string but passes if I click it a second time.

    There must be a way to speed up the initial connection process or at least mask it. How about this... extend the time-out (why wouldn't it already be set long enough?) and select an Initial Screen (I don't currently have an Initial Screen selected). Would that make it appear more like it's taking the load of the program a little longer and not the first selection that takes the time? I could more easily justify a longer load time as being due to the advanced functionality then I could sell an initial connection reason.

    Thanks for your input.

     

     
    Saturday, January 19, 2013 10:55 AM
  • Quite possibly something is slow as regards name resolution or authentication on the networking side. You would have to try looking at the network packet respone times to track down which part of the handshaking is taking the time. It could be sqlserver host -> ? but more likely client -> ?

    If you get a chance to check, let us know the outcome.

    Glenn is correct but like youself, most people would want to get that initial connection time down to 10 - 20s.


    Dave Baker | Xpert360 blog | twitter : @xpert360 Opinions are my own. Please mark as answer if this helps solve your problem.


    • Edited by Xpert360 Saturday, January 19, 2013 11:34 AM
    Saturday, January 19, 2013 11:32 AM
  • Correct, It could be trying to do a Kerberos connection failing at that then trying to set up an NTLM. You might want to swap the connection string to use a sql based logon and try that.

    Some things to check are the time sync of the domain and the servers in question, they should be all the same. Next make sure that the SPN's are all configured correctly for the domain, servers and service accounts in question.

    Sunday, January 20, 2013 2:11 AM
  • I agree with Glenn & Dave. It sounds like a network-related issue, rather than a LightSwitch issue per se. LightSwitch is simply getting an initial connection timeout for some reason.

    It's not normal for a LightSwitch application to encounter this every time it starts.


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Monday, January 21, 2013 2:53 AM
    Moderator
  • Additional

    I have a development PC and a SQL Express PC (not members of the Domain) where I develop and then I publish to the Domain SQL server. It is after the publish to the domain server that the timeout problem occurs.

    I am not using security of any kind in the LS application and the authentication is setup as SQL with a single SQL user in the logon script.

    The timeout does not occur in the development environment (workgroup) only in the domain environment.

      
    Monday, January 21, 2013 11:53 AM
  • I found the problem and you won't believe what it was. (Well... Google found the problem).

    Apparently when you install SQL Server Express 2008 R2 (I don't know about other versions) it creates a default instance that will listen on the default port 1433. However, if you create a new instance, it sets up a dynamic port and listens on a port assigned by SQL Express but I have no idea what criteria it uses to determine that port. Mine was port 49196 but it can be anything (within some range that I don't know).

    Inasmuch as the new named instance is not listening on the standard port, opening the standard SQL port on the firewall won't work. You can either open the port that SQL Express is listening on or you can change the port SQL Express is listening on.

    To find which port SQL is listening on or the change the port SQL is listening on, open SQL Server Configuration Manger and expand SQL server network configuration. Click on Protocols for SQL Express and then right click on TCP/IP in the right panel and select properties. Click on the IP addresses tab and then scroll all the way to the bottom and find the IP ALL section. There you will find the TCP Dynamic Port your instance of SQL Express is listening on. Either open that port on your firewall or assign a different port to the TCP Dynamic Port. (make sure the port you assign is open for connections).

    That's it. The fail on the first connection attempt problem will be gone. But don't ask me why it always worked on the second attempt before the port was assigned properly... I have no clue.

          

    • Marked as answer by Jyuma1 Thursday, January 24, 2013 5:53 PM
    Thursday, January 24, 2013 5:53 PM
  • Hopefully your faith in LightSwitch is 'restored' ;)

    BTW If anybody is noticing a less than optimal appearance and behaviour of the MSDN/TechNet forum software it is being vociferously discussed over in the support forum! The LightSwitch forums are just 'collateral damage'.


    Dave Baker | Xpert360 blog | twitter : @xpert360 Opinions are my own. Please mark as answer if this helps solve your problem.


    • Edited by Xpert360 Thursday, January 24, 2013 7:37 PM
    Thursday, January 24, 2013 7:36 PM
  • By default the sql express installer will install to a named instance of sql server called SQLEXPRESS and yes it uses a dynamic port. But with named instances you also need to make sure that the SQL Browser service is running and on the firewall you have opened 1433 and 1434 When you connect to a default sql connection the browsr service will kick in and redirect you to the required dynamic port.

    When installing a standard default instance you do not need the browser service, but in the case of a named instance you do.

    Also just an additional note, there may be times that when you are using SQLEXPRESS and a user instance of the database, i.e. attaching the files at run time, you can get a delay in the connection and a time out as the file is getting attached. Generally this is becuase of resource usage on the sql instance and the attach process taking longer than the timeout value... which again an be changed in the connection string at the client level.

    Thursday, January 24, 2013 9:05 PM
  • Thanks for your response Glenn...

    I lay no claim to being a SQL Express expert by any stretch but I can assure you of this... my SQL Express 2008 R2 install was using Dynamic port 49196 and every time I attempted to access SQL from my application I would receive a timeout failure but only on the first access.  All subsequent access was fine.  I experienced this behavior both on my development setup in my office and at the clients location on his SQL Server 2008 Express R2.  In both cases I changed the dynamic port assignment as described above and the problem was gone.  I can't explain it... but that is what happened.

    Thanks for your help. :)  

    Thursday, January 24, 2013 11:47 PM
  • Nice explanation Glenn!

    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Friday, January 25, 2013 2:35 AM
    Moderator
  •  @Glenn... after reading your excellent explanation again I think I may have only half understood the problem, which caused me to arrive at a wrong conclusion.

    If memory serves, don't all standard IP applications listen on a well-known port and once the initial connection has been made negotiate for the port assigned to be used for the duration of the session?

    You mentioned port 1433 and port 1434. Perhaps my problem was actually caused by only having port 1433 open and not 1434. I haven't done any further testing but is it possible that had I simply opened port 1434, the dynamic port that SQL Express specified in the IP ALL section would have automatically been assigned for the remainder of the session?

    I would not want my clumsy initial description of the problem to have caused unnecessary confusion.

    Thanks for your help.

      
    Friday, February 1, 2013 12:42 PM