Azure Key Vault auth w/ MS SQL about sql_exporter HOT 16 CLOSED

@jdstone the new release is here - v0.9.3 👍

from sql_exporter.

@burningalchemist the original bug reporter on that issue never reported back if the fix I implemented worked for them. Just reporting here since @jdstone also added a comment on that issue.

I believe the issue here is that when connecting to AzureSQL instances, you need to use a different driver name, per the driver's documentation. The change I made to fix the issue in usql is here. It just checks if there is a fedauth in the query string, and if so, uses the different driver name.

As I do not have an Azure account, nor have a Active Directory setup for it, I'm not able to test if these changes worked for usql.

from sql_exporter.

Please note that in dburl, it always returns the same driver name for a driver. As such, the using code needs to add the logic after doing a dburl.Parse. It won't work with a simple dburl.Open call. I can, and probably should, change that in dburl.

from sql_exporter.

@jdstone The fix is merged. May I ask you to try it before we create a release? You'd need to build the sql_exporter binary with make build command on the latest commit. 👍

from sql_exporter.

@burningalchemist, that worked! I was able to successfully connect to the Azure SQL Server using an Active Directory Managed Identity (a user-assigned managed identity).

The connection URL I used looks similar to this:

"sqlserver://xxx.database.windows.net:1433?database=xxx&fedauth=ActiveDirectoryManagedIdentity&user id=client_id_of_managed_identity"

Thank you for your hard work and assistance on this! I'll be looking forward to the release!

from sql_exporter.

@jdstone Fantastic! Thanks for your contribution helping this fix happen! 🎉

I'll create a release in the upcoming days or sooner. 👍

from sql_exporter.

Hey @jdstone,

1. Take a look at this PR - #148, it has some reference links for Azure AD authentication.

2. The driver list can be found here - https://github.com/burningalchemist/sql_exporter/blob/master/drivers.go#L10. Specifically for your case, it'll be github.com/microsoft/go-mssqldb/, so it's maintained by the Microsoft team.

3. For multiple database instances, you have to use multiple sql_exporter instances, it's small enough, though. And you can always build your own image, keeping only MS SQL drivers. This should reduce the image even more. It's possible to use jobs configuration that allows specifying multiple connection targets. However, the needs more testing and tuning. On the usability side, you cannot provide the targets via the environment variable with that approach.

Please let me know if you have more questions, or feel free to close the issue. 👍

from sql_exporter.

Thank you for your response @burningalchemist. I'll re-open it if I end up having further questions.

from sql_exporter.

Hi @burningalchemist,

I'm finally getting time to try this out. I read the notes here. I added fedauth=ActiveDirectoryManagedIdentity to the end of my DSN, along with the user id, which from what I can gather is the Client ID of the Managed Identity. I know you don't have an Azure environment to test this, but does the format of the Data Source Name look correct? Am I understanding the go-mssqldb documentation correctly?

sqlserver://server.database.windows.net:1433?database=db_name&fedauth=ActiveDirectoryManagedIdentity&user id=client-id

from sql_exporter.

Hey @jdstone, I think if we you with fedauth, you have to use azuresql:// as a scheme instead of sqlserver://. Give it a go? Looking forward to your feedback. 👍

from sql_exporter.

I tried it with azuresql://, but it just returns with the following when I navigate to the /metrics page.

azuresql://dbserver.net:1433?database=db_name&fedauth=ActiveDirectoryMSI&user id=tyu9qa32-k02p-53b2-h7e0-iowv1hgec310

No metrics gathered, [from Gatherer #1] mssql: login error: Login failed for user 'tyu9qa32-k02p-53b2-h7e0-iowv1hgec310'

tyu9qa32-k02p-53b2-h7e0-iowv1hgec310 is the client id of the managed identity.

Any ideas on what I could be doing wrong?

Thank you for your continued help @burningalchemist.

from sql_exporter.

Hey @jdstone, this error makes sense to me but I'd call it a configuration issue using the mssql driver. I'd try to follow this issue - #138 and the subsequent issue the user created in xo/usql#380. You might find the connection string they're using.

In other words, try to connect using the xo/usql client - we use the same interfaces and drivers. I believe it should help to debug the connectivity. Also requesting logs on the other side to understand what's missing or wrong.

from sql_exporter.

@kenshaw Thanks for the update. 👍 Yeah, I'm in the same situation regarding Azure infrastructure.

We'll see, if anyone would let me in (there are some options), I'll try to debug the connection, and inform you too. 🙂👍

from sql_exporter.

Hey @jdstone, this error makes sense to me but I'd call it a configuration issue using the mssql driver. I'd try to follow this issue - #138 and the subsequent issue the user created in xo/usql#380. You might find the connection string they're using.

In other words, try to connect using the xo/usql client - we use the same interfaces and drivers. I believe it should help to debug the connectivity. Also requesting logs on the other side to understand what's missing or wrong.

@burningalchemist
My apologies for not responding the other day. This is exactly what I did -- got it working with usql, as you can already see from the discussion. So thank you for that!

Is it possible you just need to add the logic @kenshaw suggested? If so, after you do that, I can test for you. Let me know.

from sql_exporter.

Hey @jdstone, noticed your message, that you've successfully connected usql to your Azure SQL Server. I'll analyze what @kenshaw suggested and let's test it shortly. I can provide you a couple of debug builds.

I'm a bit busy this week, but I'll try to take a look next week. 👍 Thank you! :)

from sql_exporter.

Thank you @burningalchemist. I'll attempt to take a look at this and test over the next 3-5 days.

from sql_exporter.