Comments (16)
@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,
-
Take a look at this PR - #148, it has some reference links for Azure AD authentication.
-
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. -
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.
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.
Related Issues (20)
- Different collector file for each differrent job HOT 1
- Allow to set metric timestamp from query results
- Caching context deadline exceeded result HOT 6
- Context Deadline Exceeded HOT 9
- Support for MSSQL named pipe (np:) and shared memory (lpc:) DSNs HOT 16
- `up` metric to show that database connection was successful HOT 5
- parameter to set log file location HOT 8
- Add custom metrics to expose MSSQL server hostname HOT 1
- can we have the Postgres collector yml file example
- Is it possible to distinguish between the sql exporter process status and the target database? HOT 3
- Scrape each job separately HOT 2
- [helm] Support getting data-source-name from an existing secret HOT 12
- Ignore no rows returned HOT 6
- Better handling of NULL return values in certain cases HOT 6
- Environment variable substitution for DSN's HOT 3
- Cache Mechanism for sql exporter HOT 2
- clickhouse `context deadline exceeded` HOT 1
- Helm chart improvement: additionalContainers HOT 5
- Can it support the collection of cloud databases HOT 1
- unable to use -web.listen.address flag HOT 4
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sql_exporter.