topic Re: MS SQL Server ODBC Connection in Cisco ISE in Network Access Control

MS SQL Server ODBC Connection in Cisco ISE

jwmolenaar — Wed, 17 Aug 2016 13:17:07 GMT

Hi Folks,

Since ISE 2.1 ODBC connections are possible. We are about to figure out if we could use our SCCM database during MAB authentication of our desktop.

It seems that only unnamed SQL Instances are allowed. Does anyone know if this is the case? If named instances are possible how do I have to define it? Characters like "/" and "\" are not allowed...
If someone have experience with the ODBC connection in a production environment, could you please share your experience regarding SQL response times and the overall authentication latency?

I'm lookup forward to the responses.

Best Regards, Jan-Willem Molenaar

Re: ODBC Connection Cisco ISE

hslai — Wed, 17 Aug 2016 15:11:52 GMT

Try using port number as "\" does not pass input validation in the Hostname/IP[:port] field.

MSDN …> Connecting to SQL Server with the JDBC Driver > Building the Connection URL says in the section Named and Multiple SQL Server Instances

SQL Server allows for the installation of multiple database instances per server. Each instance is identified by a specific name. To connect to a named instance of SQL Server, you can either specify the port number of the named instance (preferred), or you can specify the instance name as a JDBC URL property or a datasource property. If no instance name or port number property is specified, a connection to the default instance is created.

Re: MS SQL Server ODBC Connection in Cisco ISE

jwmolenaar — Wed, 17 Aug 2016 15:25:51 GMT

Hi,

Thanks for your reply.

That information is well know but it looks that Cisco ISE does not support the named instance. According the documentation I should user [serverName[\instanceName]. As you can see in the image below ISE does not accept the entered values.

As soon as I use a "\" it doesn't accept the configuration

Re: MS SQL Server ODBC Connection in Cisco ISE

hslai — Wed, 17 Aug 2016 15:29:02 GMT

Right. The MSDN doc mentions it's preferred to use port number.

Re: MS SQL Server ODBC Connection in Cisco ISE

jwmolenaar — Wed, 17 Aug 2016 15:37:51 GMT

If no portnumber is explicitly mention the default port number is being used.

We also tried an ODBC connection with an unnamed instance, directly on IP and directly on DNS name, that works very well.

The production environment is based on named instances. I'm figuring out if Cisco ISE can/should support named instances.

Re: MS SQL Server ODBC Connection in Cisco ISE

hslai — Wed, 17 Aug 2016 15:45:14 GMT

Correct, the default MS SQL server port is used if not specified.

Are you unable to identify the port number of your named instance since it's not the default instance? MSDN seems implying a named instance will have its unique port number.

For optimal connection performance, you should set the portNumber when you connect to a named instance. This will avoid a round trip to the server to determine the port number. If both a portNumber and instanceName are used, the portNumber will take precedence and the instanceName will be ignored.

Re: MS SQL Server ODBC Connection in Cisco ISE

hslai — Wed, 17 Aug 2016 16:03:34 GMT

On the performance, it's more to do with monitoring and tuning the store procedures and database because it's an external data source. It's a new feature in ISE 2.1 so we have no much feedback.

Re: MS SQL Server ODBC Connection in Cisco ISE

vibobrov — Wed, 17 Aug 2016 18:27:14 GMT

Named instances in MSSQL rely on named pipes. From the looks of it, ISE will only be able to connect to MSSQL using TCP/IP. You simply need to configure your instance to respond to TCP/IP in addition to named pipes.

You should be able to specify the hostname rather than the IP. ISE just needs to be able to resolve it to the IP address responding to TCP/IP. In the most likeliness, you will need to specify a full FQDN and not the short name of the server,

Re: MS SQL Server ODBC Connection in Cisco ISE

jwmolenaar — Wed, 17 Aug 2016 18:47:49 GMT

Hi Victor,

The ODBC connection is working fine with DNS and unnamed SQL instances.

But as soon as a named SQL instance is involved it doesn't work.

So DNS and/or enabling TCP/IP within SQL are both covered.

Do you have any ideas?

Re: MS SQL Server ODBC Connection in Cisco ISE

hslai — Wed, 17 Aug 2016 19:26:32 GMT

Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)

Does that not work for you?

Re: MS SQL Server ODBC Connection in Cisco ISE

jwmolenaar — Thu, 18 Aug 2016 08:58:39 GMT

Thanks all for your valued information. After an evening digging into SQL I found a solution. I'm not an expert on SQL or databased at all so any feedback is appreciated.

It turned out that SQL Aliases did the main trick for me. I created an Alias and point that to my named instance.

Because of my lack of knowledge I created this configuration for both 32 bits and 64 bits SQL Native Client 10.0 Configuration. I also don't know if the Cisco ISE solution is depended on one of the two

Secondly I created a DNS A record for my alias 'JWM' and used the IP address of the SQL server. Afterwards I verified that a CNAME is also valid.

As last item I changed the ODBC connection in Cisco ISE to use the defined Alias.

Re: MS SQL Server ODBC Connection in Cisco ISE

Richard Lucht — Wed, 24 Oct 2018 21:31:07 GMT

Do you have this working? We are trying to use MS SQL to store MAC address that pulls macs from various sources such as SCCM and Printer logic. We are having trouble with the procedures. Would you mind sharing how you have your table and procedures configured?