Skip to main content

 none
Very Simple UWP App Not Connecting To Local SQL DB With Seemingly Correct Connection String RRS feed

  • Question

  • A simple UWP App to CW a message to show proof of concept that I can connect to my local sql server (SS17 ssms17). The app throws an exception before opening. The sql server does allow remote access, and enterprise authentication is turned on.

    I am using windows authentication to connect to the server.

    The connection string has the correct server name (my local computer) and database name (SommersMarket).

    Server name = DESKTOP-K99E9GR\SQLEXPRESS

    Database name = SommersMarket

    Here is my simple and only class in the UWP app.

    namespace Ado.NetTestApp
    {
        class DBManager
        {
            public string ConnectionString = @"Data Source=DESKTOP-K99E9GR\SQLEXPRESS;Initial Catalog=SommersMarket;Integrated Security=SSPI";
            public void connectionInformation() //method to show proof of connection
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();
                    Console.WriteLine("State = {0}", connection.State);
                }
            }
        }
    }

    Here is MainPage.xaml.cs

    namespace Ado.NetTestApp
    {
      
        public sealed partial class MainPage : Page
        {
            DBManager db = new DBManager();
            public MainPage()
            {
                this.InitializeComponent();
                db.connectionInformation();
            }
        }
    }

    Error message that appears before the app opens.

    System.Data.SqlClient.SqlException: 'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 25 - Connection string is not valid

    Exception details:

    System.Data.SqlClient.SqlException
      HResult=0x80131904
      Message=A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 25 - Connection string is not valid)
      Source=Core .Net SqlClient Data Provider
      StackTrace:
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling)
       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, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.Open()
       at Ado.NetTestApp.DBManager.connectionInformation() in C:\Users\tyler\source\repos\Ado.NetTestApp\Ado.NetTestApp\DBManager.cs:line 18
       at Ado.NetTestApp.MainPage..ctor() in C:\Users\tyler\source\repos\Ado.NetTestApp\Ado.NetTestApp\MainPage.xaml.cs:line 29
       at Ado.NetTestApp.Ado_NetTestApp_XamlTypeInfo.XamlTypeInfoProvider.Activate_0_MainPage() in C:\Users\tyler\source\repos\Ado.NetTestApp\Ado.NetTestApp\obj\x86\Debug\XamlTypeInfo.g.cs:line 226
       at Ado.NetTestApp.Ado_NetTestApp_XamlTypeInfo.XamlUserType.ActivateInstance() in C:\Users\tyler\source\repos\Ado.NetTestApp\Ado.NetTestApp\obj\x86\Debug\XamlTypeInfo.g.cs:line 382

    Inner Exception 1:
    SocketException: The operation completed successfully

    Do I have a simple syntax error here?

    How may I handle this exception?

    Thank you.

    Sunday, January 28, 2018 8:14 PM

Answers

  • I have been using SQLite. I know it can work. However the article I used has been "improved" to use EF Core and/or some other core and I can't get it to work. So even SQLite in UWP is a work in progress. I have not tried to do it without EF. I have a version of that article that describes use of SQLite with EF "Framework" (not core) in a UWP application that works but I have not published it.

    I think your question is about the Northwind database, not UWP. So if a moderator moves this thread then do you agree it should be moved to the appropriate forum for the Northwind database?



    Sam Hobbs
    SimpleSamples.Info

    Monday, January 29, 2018 1:25 AM

All replies

  • Where does it say that SQL Server works for UWP? A few months ago only SQLite was supported for UWP. Someone said that Microsoft has announced that SQL Server can be used but I forget where that announcement is at and there might be differences in how SQL Server is used or there might be something more we need to do. This is the wrong forum for UWP applications. See Developing Universal Windows apps and be sure to read Guide to posting: subject line tags.

    SQLite is now being provided with Windows so consider using it.



    Sam Hobbs
    SimpleSamples.Info

    Sunday, January 28, 2018 11:07 PM
  • Hey Sam! Thanks for the reply. As Microsoft's article here states if I am interpreting it correctly, https://docs.microsoft.com/en-us/windows/uwp/data-access/sql-server-databases, UWP now can directly access SQL server. Unfortunately, actually doing that is proving to be difficult (for me). The example is a bit hard for me to follow.
    Sunday, January 28, 2018 11:27 PM
  • I have problems with Microsoft articles like that too. I tried to follow the instructions and I can't get as far as installing the Northwind sample database. I never have been able to install the Northwind sample database, the instructions assume we are experienced with doing it. There seems to be at least one critical step left out.

    In Visual Studio, in "Server Explorer" or "SQL Server Object Explorer" are you able to see the Northwind database? I cannot and I suspect that they have left something out of the instructions. If you cannot then you probably cannot get a connection to the database.



    Sam Hobbs
    SimpleSamples.Info

    Monday, January 29, 2018 12:53 AM
  • No I can't see the Northwind DB... Although if there is more support for SQLite, then I'll try to move in that direction if that is the best thing to do. I haven't worked with it all all yet (SQLite), so I didn't want to take that step yet... but it looks like I may have to lol. 

    My main concern: the users with the application will need to adjust inventory at the same time, so I think I'll have to use a client/server database engine. The application I'm working on is for inventory management. The store will need to place an order even if the inventory is being edited by someone in the warehouse at that same moment. Does that make sense? The warehouse and store work with the same information, and need to access it at the same time.


    Monday, January 29, 2018 1:00 AM
  • I have been using SQLite. I know it can work. However the article I used has been "improved" to use EF Core and/or some other core and I can't get it to work. So even SQLite in UWP is a work in progress. I have not tried to do it without EF. I have a version of that article that describes use of SQLite with EF "Framework" (not core) in a UWP application that works but I have not published it.

    I think your question is about the Northwind database, not UWP. So if a moderator moves this thread then do you agree it should be moved to the appropriate forum for the Northwind database?



    Sam Hobbs
    SimpleSamples.Info

    Monday, January 29, 2018 1:25 AM
  • Sure. That sounds great. Thanks for your help Sam. I have some concerns about the directions I'm headed, but this is the next step in figuring that out!
    Monday, January 29, 2018 1:31 AM
  • Here is something to try. It might not help but at least it tells us what is in our system. See How can I install the Northwind database into SQL Server Local DB? - Stack Overflow. In my system the  versions of SQL Server are 80-140. The SqlLocalDB is in the 130 version only. When I execute it as described in the Stackoverflow I get:

    C:\Users\Sam>"C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlLocalDB.exe" i
    MSSQLLocalDB
    ProjectsV12
    ProjectsV13

    So I think that means "(localdb)\sqlexpress" won't work and that is what they tell us to use, right? I am not sure what to try next but I think that is a clue.



    Sam Hobbs
    SimpleSamples.Info

    Monday, January 29, 2018 1:56 AM
  • You should be able to use SQL Server but just not the tutorial with the Northwind database until you are able to get it workingit. If you use SQLite then most of what you learn will apply to SQL Server too.


    Sam Hobbs
    SimpleSamples.Info

    Monday, January 29, 2018 5:59 AM
  • Okay thanks a lot for your help. I will look into SQLite in more depth. I'm too stressed out about the back end of this project I'm working on... So I'll see if SQLite helps with that. I can't seem to get this SQL server to work with anything in UWP. PS.... I think I sent you an e-mail lol.
    Tuesday, January 30, 2018 3:04 AM
  • If the email does not get through then send something to Sam at my website shown below.


    Sam Hobbs
    SimpleSamples.Info

    Tuesday, January 30, 2018 3:35 AM
  • You can try my article Getting Started with EntityFramework on Universal Windows Platform (UWP) when you feel like it. I hope it is less frustrating.


    Sam Hobbs
    SimpleSamples.Info

    Tuesday, January 30, 2018 5:20 AM
  • Hi!

    I believe this problem is because lack of Private Network (Client & Server) in the Package.appxmanifest.

    The Sql Server have many ways and protocols to connect, if you to use "TCP/IP" without domain or active directory, the most common, this capability above allow the app to connect LAN TCP/UDP layer.

    The "Enterprise Authentication" seems useful only to enterprise domain scenarios, or active directory.

    Works for me, and I don't use the EF, only pure SqlClient and Sql Commands.

    Friday, October 26, 2018 4:59 AM