Datamart (SQL) Server Connection String Details - Port number? Share email?

[JGrant8708]

Dear Community, 
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

[CraigHumphrey]

I'm seeing a similar problem from inside a client's network:

Microsoft.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=142; handshake=14867;
---> System.ComponentModel.Win32Exception (258): The wait operation timed out.
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
at Microsoft.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(SqlConnectionEncryptOption encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired, Boolean tlsFirst)
at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnectionString connectionOptions, Boolean withFailover)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionFactory.<>c__DisplayClass48_0.<CreateReplaceConnectionContinuation>b__0(Task`1 _)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<>c__DisplayClass30_0.<<OpenAsync>b__0>d.MoveNext() in /_/src/Microsoft.SqlTools.ManagedBatchParser/ReliableConnection/ReliableSqlConnection.cs:line 313
--- End of stack trace from previous location ---
at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.TryOpenConnection(ConnectionInfo connectionInfo, ConnectParams connectionParams) in D:\a\_work\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 695
ClientConnectionId:eca8d690-11b9-486a-a193-92e6a9fee26e
Error Number:-2,State:0,Class:11

I get basically the same error from both ADS (v1.42.0) and SSMS (v19.0.2)

Unfortunately I can't connect to their PowerBI/DataMart instances from outside their network, as this is blocked.

It's quite possible that they've blocked (firewalled) some part of the handshake...

Any ideas?

Thanks

Craig

[marekr]

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.

[bharath4552]

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

[JGrant8708]

Thanks @marekr 

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)

Cheers! James

[marekr]

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

[JGrant8708]

Hey @marekr 

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? 
Cheers, James

[robarivas]

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.

[marekr]

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.

[robarivas]

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)?

[marekr]

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.

[marekr]

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.

[JoePBI11]

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)"

[JGrant8708]

Hey @JoePBI11 
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!

[robarivas]

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?

[JGrant8708]

Hey @robarivas 
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? 
Cheers! James

[robarivas]

No configuration choices made except when given the credential choices between Windows, Database, and Microsoft account I chose Microsoft account.

[JGrant8708]

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. 

[marekr]

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.

[JGrant8708]

Thanks @marekr 

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. 
Cheers, James

[JGrant8708]

Hey @marekr 
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. 
Cheers, James