Forum Discussion
Datamart (SQL) Server Connection String Details - Port number? Share email?
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
21 Replies
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.
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! JamesHi 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
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 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?
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! JamesNo configuration choices made except when given the credential choices between Windows, Database, and Microsoft account I chose Microsoft account.
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)"
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!
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.
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:11I 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
