Category Archives: Sql Server

Entity Framework generate script for specific migration (aka EF Cheat Sheet)

I’ve sometimes needed to generate a specific SQL Script to apply a specific Entity Framework migration to my database.

This post is mostly for my record of how to do this, and here is the link to where I found the solution. It’s near the bottom of the page.  One day I will re-write the instructions here in a cleaner format, but for this will help me find it again, and maybe anyone else who is looking.

Here’s the link: entity-framework-migrations-cheat-sheet

Opening up Windows Firewall to allow Sql Server to Allow Remote Connections

(Updated 2016-10-24)

Intro

If you want to allow remote computers on your network to access an instance of Sql Server running on one machine, you’ll have to add some new Incoming rules to the Windows Firewall, or no connections can come through. So, let’s show you what has to be considered and how to achieve this.

Sql Server Instance Types

Before you can configure the Windows Firewall to allow remote access to your Sql Server instance, it’s important to understand the “instance type” of the Sql Server that you are running.  For Sql Server, its “instance type” can either be the default instance, an unnamed instance, or a named instance. Which one you have determines which port you have to open in Windows Firewall.

  • When SQL Server is installed as the default instance, it does not require a client to specify the name of the instance to make a connection. The client only has to know the server name. This typically uses Port 1433 in the TCP protocol.
  • A named instance is identified by the network name of the computer plus the instance name that you specify during installation (i.e. \\ComputerName\SqlExpress or something similar). The client must specify both the server name and the instance name when connecting. This typically uses the Sql Server Browser service to find the Sql Server instance, and that requires access to Port 1434 UDP protocol.

Windows Firewall Setup to Allow Remote Access

So, in order to allow remote access to the Sql Server instance, you will need to add these Incoming rules to the Windows Firewall:

  1.  Add an Incoming rule to all access the application SqlServr.exe (C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Binn\sqlservr.exe)
  2. If you want to access the Sql Server Instance from other computers by using the computer name (i.e. {ComputerName}\SQLEXPRESS, you will need to add an Incoming rule to allow access to the application SqlBrowser.exe (C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe).   Note: If you are only going to use the IP address {xxx.xxx.xxx.xxx}\SQLEXPRESS, then you will not need this rule for SqlBrowser.exe.
  3. Add an Incoming rule for the correct port (1433 TCP or 1434 UDP depending on Sql Server instance type (from above)).

You can use these handy Poweshell scripts to add the above Firewall rules:

You should see the new rules here:

windowsfirewallrulesforsqlserver

Sql Server Full version

For the full version of Sql Server (not Express), by default it installs as the default instance unless you specify an instance name. So, the next step to allow remote connections to the default instance is to add a Port Rule to open Port 1433 for the TCP protocol.

Sql Server Express

SQL Server Express always installs by default as a named instance unless you force a default instance during setup.  So, if you are running a named instance of Sql Server (or Sql Server Express), and you want to allow remote connections to it, add an Incoming port rule for Port 1434 for the UDP protocol.

Since I was running a named instance, I found that I did not need the 1433 TCP port rule at all, I only needed the 1434 UDP port rule (along with the two application rules) to get it working on my network.

Other steps to check

This post only covers the Windows Firewall. There are other steps required in configuring Sql Server itself:

  • Enabling the correct protocols, and make sure Sql Server has the option “Allow remote connections to this server” checked on the Connections tab of the Server properties. You can access this setting in Sql Server Management Studio tool.

sqlserverpropertiesallremoteconnectionstothisserver

  • Enable the correct protocols in the Sql Server Configuration Management Tool, under the Sql Server Network Configuration node. Honest, I wasn’t sure exactly which I needed, so I enabled all 3.  I’m pretty sure TCP/IP was disable by default.

sqlserverconfigurationmanager_1

 

Some details pulled from these links:

https://technet.microsoft.com/en-us/library/ms165614%28v=sql.90%29.aspx

and a comment by user J_m on this Technet article: https://technet.microsoft.com/en-us/library/ms175043%28v=sql.110%29.aspx

SSMS – Generate Scripts – Data only – Cyclic dependencies found error

When trying to generate data only scripts from a Sql Server 2012 database, I was getting this error from Sql Server Management Studio 2012:

Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishException: An error occurred while scripting the objects. —> Microsoft.SqlServer.Management.Smo.SmoException: Cyclic dependencies found. at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.DependencyGraphTraversal(Int32 num, Dictionary2 dictionary, List1 sortedList, HashSet1 visited, HashSet1 current) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.DependencyGraphTraversal(Int32 num, Dictionary2 dictionary, List1 sortedList, HashSet1 visited, HashSet1 current) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.DependencyGraphTraversal(Int32 num, Dictionary2 dictionary, List1 sortedList, HashSet1 visited, HashSet1 current) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.DependencyGraphTraversal(Int32 num, Dictionary2 dictionary, List1 sortedList, HashSet1 visited, HashSet1 current) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.DependencyGraphTraversal(Int32 num, Dictionary2 dictionary, List1 sortedList, HashSet1 visited, HashSet1 current) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.SortDictionary(Dictionary2 dictionary) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.SortDataSet(DataSet ds) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.SortDataSet(List1 objectList, Dictionary2 idDictionary, DataSet ds) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.ExecuteQueryUsingTempTable(List1 objectList, List1 list, String query) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.ResolveTableOnlyDependencies() at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.ResolveDependencies() at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.Order(IEnumerable1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List1 urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(Urn[] urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.DoScript(ScriptOutputOptions outputOptions) — End of inner exception stack trace — at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

 

This database was originally on Sql Server 2008, and I could script it without any problems at that time, which I did many times. Then, about a month ago, we moved it to Sql Server 2012, and when I tried to script the database again, using Sql Server Management 2012, I got this error.

I googled around and found this helpful script to find the offending circular references:

https://azure.microsoft.com/en-us/blog/finding-circular-foreign-key-references/

… which I ran in a query window to see what was wrong. Sure enough, it showed me the circular references and the results were spot on, but I wanted to keep the references in place. Everything worked fine when accessed from Entity Framework, and I was afraid to mess with the relations.   (In fact, the database was created in Sql Server 2012 by the Entity Framework using the update-database command. So, all the relations, foreign keys, indexes, etc came originally from the class definitions in EF.)

Problem Solved!

Then, on a whim, knowing that I had successfully scripted this database before, I connected to the Sql Server 2012 instance using Sql Server Management 2008 R2, and I was able to generated the scripts without error!!

Note, that I did data-only, and I had to allow it to script all database objects. I tried to select only certain tables, but that gave some other errors that I wasn’t willing to track down, so I just did the whole thing.

 

So, good luck to you… I hope this will help the next person who faces this problem.