Security

Reply
Occasional Contributor II
Posts: 21
Registered: ‎03-16-2015

Need help with an external SQL authentication source query

[ Edited ]

I'd be very garateful if someone can help me with an authentication source (mssql database) query,

the one I'm implementing isn't working well and in the access tracker it shows that it can't fetch my attributes.

The database name is new_computers

I'm doing mac authentication based on mac addresses and authorization based on the department for each mac address.

So we have a table (new_computer) and another attribute for authentication (macaddress) and another attribute for authorization (department).

this is the one that I implemented but I'm not able to set it right

 

 

 

Appreciate your  help

 

MVP
Posts: 707
Registered: ‎12-01-2010

Re: Need help with an external SQL authentication source query

I'd start with the SQL server logging - do you see a successful connection and we need to debug the query, or do we need to work on connecting?

--Matthew

if I've helped, please give kudos
if I've provided a solution, please mark the solution so others can find it
Occasional Contributor II
Posts: 21
Registered: ‎03-16-2015

Re: Need help with an external SQL authentication source query

Hi

The connection is fine, I double checked it. 

It is just the attributes not being able to fetch as unfortunately the filter is not correct.

It would be great if you can offer some insight on the sql query itself

MVP
Posts: 707
Registered: ‎12-01-2010

Re: Need help with an external SQL authentication source query

I probably shouldn't have started this conversation - I'm only a marginal SQL user.

My first thought is just accuracy.

Looking at your opening message you refer to a table "new_computer" in a database called "new_computers".

In the picture, your query is refering to the plural (database) and I think you might want the singular (table)

 

--Matthew

if I've helped, please give kudos
if I've provided a solution, please mark the solution so others can find it
Guru Elite
Posts: 8,444
Registered: ‎09-08-2010

Re: Need help with an external SQL authentication source query

Try doing it as authorization instead of authentication. Here's a sample SQL query that checks the MAC address and returns the value of the status and ownership columns. You can then use the extra data as part of the policy decision instead of a direct auth check.

 

SELECT status,ownership FROM assets WHERE (primary_wired_mac = LOWER('%{Connection:Client-Mac-Address-Colon}')) OR (primary_wireless_mac = LOWER('%{Connection:Client-Mac-Address-Colon}')) OR (other_wired_mac = LOWER('%{Connection:Client-Mac-Address-Colon}'))OR (other_wireless_mac = LOWER('%{Connection:Client-Mac-Address-Colon}'))

 

Hope this helps.


Tim Cappalli | Aruba Security TME
@timcappalli | timcappalli.me | ACMX #367 / ACCX #480
Search Airheads
Showing results for 
Search instead for 
Did you mean: