This project has moved and is read-only. For the latest updates, please go here.

SQL Server Connection problem

Apr 12, 2016 at 5:41 PM
I am trying to integrate TicketDesk into my company intranet. The existing setup is running on Windows Server 2012 with IIS 8 and SQL Server 2014. The existing setup connects to the database and does its thing just fine.

I added a new "Application" to the site in IIS, gave it its own application pool configured as indicated, and pointed it at the appropriate folder.

I modified the web.config file inside the TicketDesk folder so that it references MSSQLLocalDB instead of "v11.0" since I'm using SQL Server 2014.

After doing all this, I go to the site, where it spins its wheels for awhile and then spits out:

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: 50 - Local Database Runtime error occurred. Cannot create an automatic instance. See the Windows Application event log for error details.)

My first question is, can I dump this localDb stuff and just use the same database connection as the rest of the website? The main problem I see with this idea is the fact that there are pre-installed tables and data in the localdb setup that would need to be moved over.
Coordinator
Apr 14, 2016 at 4:10 AM
It sounds like you are using a full instance of SQL Server 2014, or SQL Server 2014 Express Edition (that isn't LocalDB); but your connection string is configured for a user-instance database. Only SQL Server 2014 Express LocalDB Edition supports user instances though, so you just need to adjust the connection string.

The good news is that TicketDesk can use any version of SQL Server 2014, all you have to do is use the right connection string. You don't have to manually do anything manually, once you give TD a connection string it can use, EF Migrations will take care of setting everything up in the target database for you.

This is all covered in detail over on the Wiki here; including a lengthy description of the various SQL editions, what kinds of databases they support, and what connection strings for each should look like.
Apr 14, 2016 at 5:13 AM

I had already looked at that Wiki page and tried a regular connection. It didn’t work.

And the SQL Server configuration manager seems to think I have LocalDB installed along with everything else.

So… dunno where that leaves it.

Coordinator
Apr 14, 2016 at 6:21 AM
Make sure TCP/IP is enabled (also discussed in the wiki). And of course, you need to know what version and edition of SQL is installed, and if not localdb what the SQL instance name is... the source property is usually <server>/<instance> unless your instance is the "default", in which case it's just the server name..

In either case though, the error message seems to be saying that it can't find the server at all --either TCP/IP isn't enabled, the server is on a different machine and a firewall or local network security suite is blocking access, or your server and/or instance name is incorrect.

If you are unsure about the instance name, re-run the installer and it should show you the list of installed instances.
Apr 14, 2016 at 5:15 PM

The website I’m trying to do this with already has a fully functional connection to SQL Server. I used the existing connection string as the basis for modifying the ones for TicketDesk. Here’s what I had tried (usernames & passwords redacted):

<connectionStrings>

<clear />

<add name="TicketDeskSecurityConnectionString" connectionString="Data Source=PACIFICAWEB;Initial Catalog=pacifica;Persist Security Info=True;User ID=#####;Password=#####" providerName="System.Data.SqlClient" />

<add name="TicketDeskEntities" connectionString="metadata=res://*/Models.TicketDeskEntities.csdl|res://*/Models.TicketDeskEntities.ssdl|res://*/Models.TicketDeskEntities.msl;provider=System.Data.SqlClient;provider connection string=" Data Source=PACIFICAWEB;Initial Catalog=pacifica;Persist Security Info=True;User ID=#####;Password=#####;Connect Timeout=30;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient" />

<add name="ElmahLog" connectionString=" Data Source=PACIFICAWEB;Initial Catalog=pacifica;Persist Security Info=True;User ID=#####;Password=#####" providerName="System.Data.SqlClient" />

</connectionStrings>

That’s pretty straightforward, and if it doesn’t work, I have to think it’s either because of the metadata references in the second one, or because the ultimate problem isn’t the connection string.

The wiki page you pointed at only shows one example connection string. Ideally, I’d want examples of all three with placeholder strings like “<yourserver>” and “<username>” and “<password>” that can be replaced.

Coordinator
Apr 15, 2016 at 6:18 AM
Those connection strings look fine to me, so I'm inclined to agree that these may not be the problem.

Don't worry about the metadata part of the second string; that's just some ugly EF stuff but has nothing to do with the actual database connection; they just tell EF's where to find the logical model mappings, and the values should never be changed.

I'd troubleshoot first by seeing if I could connect using these settings from my local system using SQL management studio; or if you have that installed on the web server try it from there (even better). Try swapping the server name for the IP address, try ip and port (1433 usually), try using the sa login. If I still can't get in, start troubleshooting from SQL server side --make sure TCP/IP is enabled in SQL Config on the SQL server. If the server has multiple SQL instances, are they bound to explicit ports or is SQL browser brokering connections. Make sure the sql login exists, make sure the sql login was given access to this specific database, and the login has db_owner permssions to the DB.

There isn't anything magic or special about TicketDesk's database connections... it's just an older, but completely standard EF connection string; under the hood EF uses ADO SqlConnection just like everything else does.