Try. Catch. Throw.

I am running a system with SQL Server 2008 R2.  Along with the default instance, I have a second SQL Server instance for another application.  What we need to do is assigned the system with 2 IP Addresses, then set one instance to one IP Address and the second instance with it's own IP Address using the same port 1433/1434.

Network Configuration

In the Local Area Properties, you need to edit TCP/IP4 Properties and add the 2nd IP Address in the IP Settings tab.

In DNS tab, Uncheck 'Register this connection's addresses in DNS'

This adds the 2nd IP Address to the system.  May require network configuration changes to forwarding or resolving the IP Address or Hostname to this systems Network Adapter.  Also may require a /dnsflush and /registerdns.

SQL Server Configuration Manager

Assuming you already installed the default and the second database instance, you will need to go to the SQL Server Configuration Manager.

First thing is to add a new Alias.  I chose the alias that was registered in the DNS to resolve the name to this systems 2nd IP Address.  You can test with NSLOOKUP or PING to see if you can reach the system.

I then set the Port number to 1433, Protocol to TCP/IP and set the Server to the second IP Address.

Protocols for the DEFAULT SQL Server Instance

Select this from the menu on the left, in the right you should see Shared Memory, Named Pipes, TCP/IP and VIA.  All except VIA should be enabled.

Open TCP/IP Properties

Protocol tab

Set Listen All to No (This disables this instance to listen for any IP requests, 127.0.0.1, 10.0.0.1)

To my understanding, when a SQL Server is listening, by default it uses the default SQL Server Instance.  If there is a call to the 2nd instance, this network configuration will response to the client with a dynamic port on 1434, then the client makes another call with the new dynamic port.  Typically networks with strict firewall rules will block those dynamic ports.

Next make changes the IP Addresses assigned to this instance.

IP1 Active: Yes Enabled: Yes IP Address: 1st IP Address (System IP Address) TCP Dynamic Ports: [Blank] TCP Port: 1433

The remaining IPs should all be set to Active Yes, Enabled No.  Most IPS will be set to 192.168.1.1 or 127.0.0.1, 10.0.0.1 or ::1.  All the TCP Dynamic Ports needs to all be cleared and TCP Port set to 1433.

Protocols for the SECOND SQL Server Instance

Everything should apply the same settings above, just ensure that the IP1 IP Address is set to the 2nd IP Address you assigned to the system.

Now test using SQL Server Management Studio (SSMS)

Test connections using the Systems Hostname and IP Address, SSMS should open the default instance.  To compare, you should be able to also connect to the default instance using localhost or localhost IP Address.

Now test your connections to the second instance using the Alias Name and 2nd IP Address.  SSMS should open the second instance.  Also you can compare by connecting using the HOSTNAME/2NDINSTANCENAME.

If you can access both instances with it's own IP Address using the same port 1433.  

I think I found my solution to my problem on web systems that require SSL and Common Access Cards.  I can’t pin-point the exact problem but I did noticed on some versions of Windows 7 and maybe 8, that the system does not required the use of ActivClient (in other words, ActivClient may not need to be installed), nor does Internet Explorer 9 or 10 seem to use the Certificate prompt used by ActivClient.  It appears that the IE browsers are forced to use Microsoft’s Smart Card client instead.

Nevertheless, the following is configurations of a Windows 7 system using Internet Explorer 10 with ActiveClient 6.2 installed.  I’ve came up with a few configuration changes you can check to see if this will help with web system applications that prompt for the pin several times.  The root cause of the pin prompts is that web systems that require SSL that have referenced links to javascript or css files in the html file, which may treat that path as a separate connection/instance, therefore since the client browser doesn’t seem to want to authenticate the connections, the additional pin prompts are for each of the referenced css/js files.  PIN Caching seems to not be working, but if Windows 7/IE10 doesn’t use ActivCard that maybe why.

First I would check smart card reader drivers - Make sure your smart card reader drivers are up to date in the Device Manager in Computer Management.

ActivClient is up to date with the latest version/patches (reboot several times)

Open Advanced Configuration Manager (systray)

In PIN Caching Service

Enable PIN caching: Yes

Enable “included” application list: Yes

Applications “included” in PIN caching (verify your browser path is in the list)

Internet Explorer Configuration Settings in Internet Options

GENERAL TAB

Delete Browser History (check the following and apply)

Preserve Favorite Website Data

Temporary Internet Website Data

History

Delete Browser History Settings

Set Disk Space to 50mb

SECURITY TAB

Add the https site you wish to access in the Trusted Sites

CONTENT TAB

In the Certificates button, select your PKI Certificates and remove them from the Personal Tab

While you’re at it, go ahead and Clear SSL State

Re-add your certificates back into personal tabs.  I did this with ActivClient agent, by selecting each PKI Certificate and clicking “Make this certificate available to Windows”.  There are other methods, I don’t know off hand what they are.

These are some basic client side configurations that I’ve done to end the annoying multi-pin prompts when accessing systems that required SSL.  There might be other causes to other configured systems which include security settings from the server side.  You may try any or all the above.  I was able to gain access to the ActivClient hot fixes through privileges I’ve received.  I will not post the client or drivers on this block, nor will I provide links on where to get them.

This experience was to prove that clients whom had multi-pin prompting issues is not related to the web system, but also the configuration of the client’s system and browser settings.

When generating DataGrid for the client, they requested the ability to download the DataGrid to an Excel Spreadsheet.  While researching, couldn’t find an efficient and cost effective method.  Spent less than a few hours to write a class object using XmlWriter and MemoryStream Classes.

Nothing fancy, just needed something that will pull either a binded DataTable or DataView with RowFilters and convert it to an Excel SpreadSheet in an XML format.

Just bare essentials… 

ExcelXMLSpreadSheet.cs Code:

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Xml; using System.Data; using System.IO; namespace Export2Excel { public class ExcelXMLSpreadSheet { /// <summary> /// Converts DataBinded System.Data.DataView to Excel XML Spreadsheet. /// </summary> /// <param name="dataTable"></param> /// <returns>UTF-8 Excel XML string.</returns> static public string DataSource2Excel(DataView dataView) { return DataSource2Excel(dataView.ToTable()); } /// <summary> /// Converts DataBinded System.Data.DataTable to Excel XML Spreadsheet. /// </summary> /// <param name="dataTable"></param> /// <returns>UTF-8 Excel XML string.</returns> static public string DataSource2Excel(DataTable dataTable) { XmlWriterSettings xmlSettings = new XmlWriterSettings(); xmlSettings.NewLineHandling = NewLineHandling.Entitize; xmlSettings.Indent = true; xmlSettings.IndentChars = "\t"; xmlSettings.ConformanceLevel = ConformanceLevel.Document; xmlSettings.OmitXmlDeclaration = false; xmlSettings.Encoding = new UTF8Encoding(false); MemoryStream memoryStream = new MemoryStream(); XmlWriter xmlWriter = XmlWriter.Create(memoryStream, xmlSettings); xmlWriter.WriteStartDocument(); // Workbook Element Start xmlWriter.WriteStartElement("ss", "Workbook", "urn:schemas-microsoft-com:office:spreadsheet"); // Worksheet Element Start xmlWriter.WriteStartElement("ss", "Worksheet", null); xmlWriter.WriteAttributeString("ss", "Name", null, "Sheet1"); // Table Element Start xmlWriter.WriteStartElement("ss", "Table", null); // Column Element Start foreach (DataColumn dc in dataTable.Columns) { xmlWriter.WriteStartElement("ss", "Column", null); xmlWriter.WriteAttributeString("ss", "Width", null, "100"); xmlWriter.WriteEndElement(); } // Column Element End int iRowIndex = 0; foreach (DataRow dr in dataTable.Rows) { // Row Element Start xmlWriter.WriteStartElement("ss", "Row", null); foreach (DataColumn dc in dataTable.Columns) { // Cell Element Start xmlWriter.WriteStartElement("ss", "Cell", null); // Data Element Start xmlWriter.WriteStartElement("ss", "Data", null); string dataType = dc.DataType.Name; // Fix this later... Excel throws exception for bad data type // Number, DateTime, Boolean, String, and Error //if (dataType.IndexOf("Int") != -1) // xmlWriter.WriteAttributeString("ss", "Type", null, "Number"); //else // xmlWriter.WriteAttributeString("ss", "Type", null, dataType); xmlWriter.WriteAttributeString("ss", "Type", null, "String"); if (iRowIndex == 0) xmlWriter.WriteString(dc.ColumnName); else xmlWriter.WriteString(dr[dc.ColumnName].ToString()); xmlWriter.WriteEndElement(); // Data Element End xmlWriter.WriteEndElement(); // Cell Element End } // Row Element End xmlWriter.WriteEndElement(); iRowIndex++; } // Table Element End xmlWriter.WriteEndElement(); // Worksheet Element End xmlWriter.WriteEndElement(); // Workbook Element End xmlWriter.WriteEndElement(); //xmlWriter.WriteEndDocument(); xmlWriter.Flush(); xmlWriter.Close(); string xmlOuput = Encoding.UTF8.GetString(memoryStream.ToArray()); return xmlOuput; } } }

Button Click Event:

The Save (Not Permitted) dialog box warns you that saving changes is not permitted because the changes you have made require the listed tables to be dropped and re-created.

The following actions might require a table to be re-created:

Adding a new column to the middle of the table

Dropping a column

Changing column nullability

Changing the order of the columns

Changing the data type of a column

To change this option, on the Tools menu, click Options, expand Designers, and then click Table and Database Designers. Select or clear the Prevent saving changes that require the table to be re-created check box.

Ensure Application Pool is .NET 4.0

In some cases, including the following into the web.config, but if you happen to encounter a 404 error passing RouteData in the url, suggest try adding the following this into the web.config

Microsoft MSDN still requires the use of the Microsoft File Transfer Manager…

While attempting to download an ISO, I receive:

“server ssl certificate is issued by a certificate authority that is unfamiliar to the client”

On the Microsoft File Transfer Manager:

Click, Options

Select, Connection tab

Download the jQuery files here (http://jqueryui.com/download/)

Expand them to the SharePoint Extensions directory, by default you can find it in:

Program Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS

I created the following folder structure:

LAYOUTS\jQuery

ui

1.9.0

themes

base

[CSS files]

[jquery.ui.*.js files]

jquery-1.8.2.js

Followed mostly the folder structure in the jQuery\development-bundle.

Use SharePoint Designer to edit the v4.master master page

When selecting v4.master in the Master Pages directory, right click on the v4.master file and select “Edit in advanced mode”

Removed onDemand=”True” parameter from

<SharePoint:ScriptLink language="javascript" name="core.js" runat="server"/>

Added these:

<SharePoint:ScriptLink Language="javascript" Name="/_layouts/jQuery/jquery-1.8.2.js" runat="server"></SharePoint:ScriptLink> <SharePoint:ScriptLink Language="javascript" Name="/_layouts/jQuery/ui/1.9.0/jquery-ui.js" runat="server"></SharePoint:ScriptLink>