locked
EF6 - SQL connection string works under IIS Express and doesn't work under IIS RRS feed

  • Question

  • User1993186009 posted

    Hi,

    I've got the following issue while running my web app under local IIS (not Express).

    "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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

    My connection string:

    <add name="DefaultConnection" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename='|DataDirectory|\KODatatableContext.mdf';Integrated Security=True" providerName="System.Data.SqlClient"/>

    The same connection string is used when I run the web app in VS2015 on IIS Express, and I have no issues in this case. What's wrong with  this connection string? The file KODatatableContext.mdf is placed in App_Data folder, that's for sure.

    Friday, June 15, 2018 8:42 AM

All replies

  • User753101303 posted

    Hi,

    Seems LocalDB is not installed on your web server or not with the same instance name. You could try https://docs.microsoft.com/en-us/sql/tools/sqllocaldb-utility?view=sql-server-2017 on your web server to check for that.

    Use :

    sqllocaldb i

    to list instance names. Or if sqllocaldb is not found, it is likely not installed.

    Friday, June 15, 2018 9:53 AM
  • User1993186009 posted

    Hi,

    The machine with VS and the machine with IIS is the same machine; how come the LocalDB is not installed there? And "sqllocaldb i" showed me expected MSSQLLocalDB.

    Friday, June 15, 2018 10:04 AM
  • User1120430333 posted

    Yes, it looks like Localdb is not installed on the machine. Also as the connectionstring stands now, the MDF file should be in C:\users\uersname is where it should be located where Localdb will look for it.  Or the connectionstring must give the file path to where the MDF file is located. 

    Take not that the MDF file should be attached to the database engine, if it is to be ever used in a multi user environment, becuase Localdb is only a single user instance meaning only one user can have the MDF file open at any given time.

    Friday, June 15, 2018 10:12 AM
  • User1993186009 posted

    Hmm, then why my MDF is successfully found in App_Data when the app is running from VS under IIS Express?  C:\users\uersname doesn't have KODatatableContext.mdf, that's for sure. And this app is only for single-user test purposes, I am deadly sure. That's why I don' understand how to use MSSQLLocalDB in such case on IIS.

    Friday, June 15, 2018 11:27 AM
  • User753101303 posted

    It seems the message clearly state the server or the instance is not found. I would expect another message if not finding the MDF file ?

    I'm not using much LocalDb beyond local testing with IIS Express. I'm fairly sure it is scoped to the user (if I remember by default MDF files are created in the user profile) so could it be that the account under which your IIS app runs doesn't have access to LocalDB ?

    Edit: https://blogs.msdn.microsoft.com/sqlexpress/2011/12/08/using-localdb-with-full-iis-part-1-user-profile/ seems to confirm that. You have a 2nd post giving solutions to solve this.

    Friday, June 15, 2018 11:35 AM
  • User1120430333 posted

    Hmm, then why my MDF is successfully found in App_Data when the app is running from VS under IIS Express?  C:\users\uersname doesn't have KODatatableContext.mdf, that's for sure. And this app is only for single-user test purposes, I am deadly sure. That's why I don' understand how to use MSSQLLocalDB in such case on IIS.

    So it all worked just fine with IIS Express, and now it doesn't work with local IIS. Did you make the MDF  file using Visual Studio's Service Based Database too? I mean how hard is it to install Localdb from its MSI file or install it from the MS SQL Server Express  installation and move on? 

    Friday, June 15, 2018 12:08 PM
  • User475983607 posted

    The connection string is using integrated security.  It works in IIS Express because IIS Express is running under your context.  It does not work in IIS because the app is running under the app domain context.

    Either...

    1. Change the app domain to run under your account.
    2. Create a service account.  Grant access to the account in SQL using SSMS and configure the app domain to run under the service account.
    3. Create SQL login and add the username and password to the connection string.
    Friday, June 15, 2018 12:19 PM
  • User1120430333 posted

    @mgebhard

    The OP is not posting any exception message, and we are all just stabbing in the dark. But I suspect that Localdb is not installed on the machine, and it's not going to get there because the OP installed Visual Stuido. I don't think.

    Friday, June 15, 2018 11:52 PM