Security

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

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?

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

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)

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.