I have created a small testing Datamart based on this brand-new functionality. Works really well in the service/browser.
Would love to test that I can connect to my PBI Datamart through ADS or SSMS and I seem to be failing on 2 counts:
1. When I Shared the Datamart with myself (i.e., I enter my own AD name and presumably send myself an email with a connection string) nothing arrives
2. When I look at the Settings of the Datamart and copy the Connection string from there it seems to be missing a port number (screenshot below)
Whilst I can imagine some email filtering is getting in the way of option (1), which I'll raise within my Organisation, I hoped that option (2) would work 'immediately'. Does anyone know if it's a standard port number? Or is it dynamically generated as part of the sharing permission?
* I tried using the port number that was displayed on the Intro video MS released and it didn't work
Many thanks, James
It's the standard port number (1433). Please make sure you use the latest SSMS and ADS versions. It won't connect with older versions. For SSMS please select "Azure Active Directory - Universal with MFA" Authentication.
By copying and pasting the link in the SSMS server name tab with Servertype as Analysis services, we are getting the Claimstoken error. we tested with SSMS version v18.9.1 and not able to access.
Can someone help me on this
I've appended ",1433" in ADS (latest version, fresh install on a new PC today) and am now receiving a connection issue:
Still progress as it's a different error at least!
I've also received confirmation that the Share email did go through to one of my colleagues, though I'm unsure if they're able to connect using it (they haven't had the time as yet).
What might loosen this security issue? (it's a Datamart in a Premium workspace that I'm an Admin of and I created/refreshed the Datamart, all using the same personal AD account)
Hi James, can you please confirm that you don't have Private Endpoints enabled for your organization in Power BI? See Private endpoints for accessing Power BI - Power BI | Microsoft Docs
I will check with IT about this point. If I had to gues I would say that we DO have Private Endpoints enabled - it seems like the type of thing the organisation would prefer to do (keep all of our traffic within the MS network until absolutely necessary).
If we DO have it enabled, what's the approach?
So in addition to Excel I've managed to connect with Azure Data Studio but cannot connect with SSMS (despite following the steps in Microsoft's official Datamart documentation) nor with any other tool.
Can you please confirm that you're using the latest version of SSMS? Only a few tools are supported right now, but SSMS is one of them and should work if you're on a recent version.
Thanks @marekr . SSMS did not work with v19 Preview. When I switched to the Generally Available version 18 it worked. Is there a way to find out what tools are supported (and any connection instructions for each)?
Thanks for letting us know about the v19 Preview issue. I've been using version 18 and that is the only supported version of SSMS right now. We'll look into why v19 Preview doesn't work.
SSMS v19 Preview uses a different Azure Active Directory application id than the one that is enabled for SSMS v18 by datamarts. We've already started the work for enabling the new application id last week. For now please use SSMS v18.
Having same issues. I have received an email once I added myself to the azure AD, but there was no connection string in the email.
I havent managed to connect to the sql db via ssms, ADS, PBI or excel. same error message each time:
"Details: "Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
I was getting this "error 40" myself, and then once I added in the port number (1433) it has moved to "error 0" (citing the connection is being made now - but being rejected by the server).
Hoping there's a switch I missed!
I would also like to know how to make that connection string work in other 3rd party tools. Using the string in Excel (via the SQL Server connector) just works. But not sure what else I need to do or what else I need to know for other tools. Typically other tools ask for the host, port, database (which is not part of the string I suppose), user id, and password. But when I input the string (not sure about port) as the host and my usual id and password I can't seem to get other tools to connect. Is there a general (or specific) guide for properly using the new Dataset string in other tools?
I can't get the Datamart connection string to work in Excel via its PQE. You say you can just pop the string in as-is? Can you share a screenshot or any configuration choices you've made?
No configuration choices made except when given the credential choices between Windows, Database, and Microsoft account I chose Microsoft account.
Interesting. That's what I've selected each time too (use the MS Account).
Elsewhere in the thread MS has noted needing Private endpoints to be configured, reading into it now.
Private Endpoints are NOT currently supported for datamart connectivity (documented in limitations), so it might explain why you can't connect, if you have them enabled for the organization.
I've logged the support ticket to my IT team and once I've confirmed our Private Endpoint status, I'll mark one of your replies as the Solution (in all likelihood).
Shall await Private Endpoint enabling at a later date! (something to look forward to ;))
Will look back.
Ticket came back - and we don't have Private Endpoints enabled.
Whilst I'm more practiced with ADS, I'll see if I can install SSMS v18 as mentioned elsewhere in this thread and get it to work.
If Private Endpoints are not enabled it might be a problem of your organization's firewall blocking outbound connections to port 1433. Could you try connecting from a device outside of your organization's network? If that also doesn't work please open a support ticket and give us some identifiers like your datamart url or endpoint you're trying to connect to.
Vote for your favorite t-shirt design now through March 28.
Find out more about the March 2023 update.
Find out more about the online and in person events happening in March!