SQL Server Setup and Configuration
TicketDesk is designed for wide compatibility with most editions of SQL Server, version 2008 or newer. This includes Express, Express LocalDB, Standard, Web, Enterprise, and DataCenter editions.
TicketDesk is not compatible with SQL Server Compact Edition (SQL CE).
Azure SQL Server is covered in a separate location here
If SQL CE support is desired it is not terribly difficult to modify the source code for SQL CE compatibility, but you will need to modify the Entity Model classes in some cases, and generate new Entity Framework Migrations for SQL CE.
Connection strings for SQL Server are a very common source of trouble for many admins, and problems related to connection strings are the most commonly discussed topic in the online discussions.
The connection string is also the only setting that requires manual editing in the site's configuration files. If not set correctly, nothing else in TicketDesk will work.
While this isn't a comprehensive guide to connection strings, we will cover the most common scenarios for TicketDesk here.
An excellent resource for information about connection strings and the various parameters can be found at connectionstrings.com
User-Instance Database with SQL Express 2014 and newer LocalDB
connectionString="data source=(LocalDb)\MSSQLLocalDB;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|TicketDesk.mdf"
User-Instance Database with SQL Express 2012 LocalDB
connectionString="data source=(LocalDb)\v11.0;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|TicketDesk.mdf"
User-Instance Database with SQL 2008 Express & SQL 2008 R2 Express
connectionString="data source=.\SQLExpress;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|TicketDesk.mdf"
SQL Logins with SQL 2008 or newer - Express, Standard, Enterprise, etc.
connectionString="data source=server/instance;database=TicketDesk;user id=TicketDeskUser;password=TicketDeskUserPwd;
Trusted Connections with SQL 2008 or newer - Express, Standard, Enterprise, etc.
connectionString=data source=server/instance;database=TicketDesk;trusted_connection=True"
LocalDB, SQL Express 2008, and SQL Express 2008 R2 support user instance databases, which are sometimes called a 'File based Database'.
What this means is that when the web application starts up, it will attach a database file to SQL Server. When the web application shuts down, it will detach the database files automatically. In web applications, the file is normally stored within the application's file system (in the /app_data folder).
User instance databases are great during development. You start your site, the database is automatically attached, and when you shut down it goes away; no mucking about with SQL management tools or configurating windows services. But user instance databases run in an application process instead of as a windows service. Since Windows server is optimized to favor services, you will notice a performance decrease on production servers that isn't apparent when using this kind of database on a developer workstation. There are also limits on how much data they can hold, and not all of SQL Servers features are supported with user instance DBs.
Fortunately, you can copy a user instance database file to regular SQL server instances, and attach them to any edition of SQL server. Physically, the database file is 100% compatible with regular SQL servers, making upgrades to more powerful SQL editions simple and easy.
User Instance databases are supported only by a few very specific SQL Server editions.
- SQL Server 2008 Express
- SQL Server 2008 Express R2
- SQL Server 2012 Express LocalDB
- SQL Server 2014 Express LocalDB
- SQL Server 2016 Express LocalDB
SQL Server 2012 Express and SQL Server 2014 Express DO NOT support user instance databases; only their LocalDB edition counterparts do. This is a common point of confusion.
SQL Express 2005 and 2008 did support both server managed databases, as well as user instance databases.
Because Microsoft hates you, in 2012 they split regular server managed databases from user instance databases into two different SQL server products.
Starting with SQL 2012, user instances databases were moved to 'LocalDB', while SQL Express would only support server managed databases. To further confuse people, they decided at the last minute to tack on the word "express" to the product names for both editions.
Connection strings for user instance databases will include the AttachDBFilename parameter, which identifies where to find the file to attach. The |DataDirectory| portion of the path will map to the /app_data folder within the web application. You can also specify UNC or full local file paths.
User instance databases only work when an appropriate instance of SQL is installed on the web server. Your application cannot connect to user instance databases that are located on a different physical server.
Another common issue people encounter is that developer and other full editions SQL Server does not enable TCP/IP by default. You will need to explicitly enable it using the SQL Configuration Manager tool.
To enable TCP/IP follow these steps:
- Open the SQL Server Configuration Manager (see image below)
- Expand SQL Server Network Configuration, then click 'Protocols for [instance name]'
- In the details pane, click TCP/IP and then change Enabled to 'Yes'
if you have 32 bit versions of SQL server installed, repeat these steps under the 'SQL Server Network Configuration (32bit) node
If you have SQL Server 2014 or 2016 installed along side any previous edition of SQL Server, then there will be two SQL Configuration Manager tools; one specifically for 2014 or newer, and the other for all earlier versions. If this is the case, you will need to enable TCP/IP in BOTH tools, though the steps are nearly identical in both cases.
You may need to enable TCP/IP even if you are running the Database and IIS servers on the same machine.
You should not need to change any other settings or specify IP addresses or specific ports in most cases
If you have multiple instances of SQL Server on the same machine, you should also enable the SQL browser service. This service is installed along with SQL Server, but is not enabled by default. The SQL browser brokers connections between multiple SQL instances. If you don't enable it, you will need to specify different IP addresses and/or ports for each SQL instance.
To enable the service:
- Open the SQL Server configuration manager (see image below)
- Choose the 'SQL Server Services' node
- In the details pane, right-click SQL Server Browser and choose properties
- Change to the Service tab in the pop-up dialog
- Set the 'Start Mode' to automatic.
If you have SQL Server 2014/2016 and earlier editions installed on the same machine, you do not need to repeat these steps in both versions of SQL Server Configuration Manager. There should only be one Browser service running on the machine.
The SQl Browser listens on port 1434, so make sure your firewall permits traffic on that port.
If your database server is not on the same machine as IIS, you will likely need to open some ports in your firewall on the SQL Server system. If you only have one instance of SQL server, just open TCP/IP Port 1433. If you have multiple instances, you should enable the SQL Browser service (see above) and also open TCP/IP Port 1434.
TicketDesk uses Entity Framework Migrations to manage, and potentially even create, its own database automatically.
For best results, the user account that TicketDesk uses to connect to the SQL server should be the db_owner for the TicketDesk database.
To create databases, this account must also have create database permissions at the server level; however, if that makes you (or your DBA) uncomfortable then you can manually create an empty database for TicketDesk to use.
The simplest way to manage security is to create a SQL Login for use with TicketDesk, and grant that user db_owner permissions to the database (and optionally give it create database permissions to the server). Then you specify the user name and password for this account as part of the connection string in the TicketDesk web.config file.
SQL Server must have been installed with the mixed mode security option in order to use SQL Logins.
To create a SQL Login that has create database rights:
- Open SQL Server Management Studio
- Connect to the SQL Server with a server admin account
- Right click the Security Node, choose New Login
- Under the 'General' section:
- Change the type to SQL Login
- Enter the login name and password
- (recommended) Uncheck the password expiration option
- Under the 'Server Roles' section
- Check the 'dbcreator' option
Again, you can skip adding the login to the dbcreator role, but you will need to manually create the TicketDesk database, and make your login the db_owner.
Make sure whatever account your IIS application pool uses has access to the SQL server, and is db_owner in the TicketDesk database. How this is done varies a bit between IIS versions, and what kind of account you are using. Remember, if you are using the standard application pool identity in IIS, and your DB is on another machine, then in SQL the user is "Domain\ServerName$".
See this post for more info.