cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Announcements
 
ISE 2.3 Patch 7 has been posted. This will be the last patch for the ISE 2.3 release!
Choose one of the topics below to view our ISE Resources to help you on your journey with ISE

This community is for technical, feature, configuration and deployment questions.
For production deployment issues, please contact the TAC! We will not comment or assist with your TAC case in these forums.
Please see How to Ask the Community for Help for other best practices.

1555
Views
0
Helpful
11
Replies
Beginner

MS SQL Server ODBC Connection in Cisco ISE

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

Everyone's tags (3)
1 ACCEPTED SOLUTION

Accepted Solutions
Beginner

Re: MS SQL Server ODBC Connection in Cisco ISE

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

Alias.PNG

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.

Infoblox.png

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

ISE.PNG

11 REPLIES 11
Cisco Employee

Re: ODBC Connection Cisco ISE

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.

Beginner

Re: MS SQL Server ODBC Connection in Cisco ISE

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

odbc-connection.PNG

Cisco Employee

Re: MS SQL Server ODBC Connection in Cisco ISE

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

Beginner

Re: MS SQL Server ODBC Connection in Cisco ISE

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.

odbc-connection-working.PNG

Cisco Employee

Re: MS SQL Server ODBC Connection in Cisco ISE

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.
Cisco Employee

Re: MS SQL Server ODBC Connection in Cisco ISE

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.

Cisco Employee

Re: MS SQL Server ODBC Connection in Cisco ISE

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,

Beginner

Re: MS SQL Server ODBC Connection in Cisco ISE

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?

Cisco Employee

Re: MS SQL Server ODBC Connection in Cisco ISE

Beginner

Re: MS SQL Server ODBC Connection in Cisco ISE

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

Alias.PNG

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.

Infoblox.png

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

ISE.PNG

Highlighted
Beginner

Re: MS SQL Server ODBC Connection in Cisco ISE

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?