Issue using linked service v 2.0 on notebook

Question

Issue using linked service v 2.0 on notebook

Lotus88 176

Hi,

I am accessing the DB in Synapse notebook with linked service version 1. It is working ok but when I changed to version 2, error occurs. Is there any additional setting I need to set in Notebook ? Thank you!

User's image

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2025-05-28T05:50:03.38+00:00

@Lotus88

It seems like you're having a bit of trouble while switching to linked service v2.0 in your Synapse notebook. Let’s troubleshoot this together.

When you change to version 2, there could be several reasons why you're encountering errors. Here are a few things you might want to check:

Configuration Settings: Make sure you configure any necessary parameters specific to linked service v2.0. There might be settings that were automatically managed in v1 but now need to be input manually in v2.

Authentication: Check if the authentication method you are using for the linked service is still valid in v2. Any API or token updates needed for v2 should be applied.

Cluster Settings: Sometimes linked services require specific cluster configurations or dependencies. Ensure that the cluster settings align with what v2 needs.

Error Details: If you can, provide more details on the error message you're seeing when trying to use v2. Specific error codes or messages can help pinpoint the issue more accurately.

Review the official documentation related to the linked service v2.0 to see if there are any additional setup steps you may have overlooked.

Hope this helps. Do let us know if you any further queries.
Lotus88 176 Reputation points

2025-05-28T06:01:23.9233333+00:00

Hi @phemanth ,

Here is the full error. I am not sure what is missing. Do you have any example how to use linked service ver 2 in notebook ? I cannot find anyway it can be done from Microsoft website. Is it supported in notebook ?

Py4JJavaError Traceback (most recent call last) Cell In[25], line 8 4 portNum = 1433 6 jdbc_url_db1 = f"jdbc:sqlserver://{serverNameSrc}:{portNum};database={databaseSrc};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30" ----> 8 token = TokenLibrary.getConnectionString("gApachub_TST") 10 conn_properties_db1 = { 11 "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver", 12 "accessToken" : token 13 } 15 print(conn_properties_db1) File ~/cluster-env/env/lib/python3.10/site-packages/py4j/java_gateway.py:1322, in JavaMember.call(self, args) 1316 command = proto.CALL_COMMAND_NAME +\ 1317 self.command_header +\ 1318 args_command +\ 1319 proto.END_COMMAND_PART 1321 answer = self.gateway_client.send_command(command) -> 1322 return_value = get_return_value( 1323 answer, self.gateway_client, self.target_id, self.name) 1325 for temp_arg in temp_args: 1326 if hasattr(temp_arg, "_detach"): File /opt/spark/python/lib/pyspark.zip/pyspark/errors/exceptions/captured.py:169, in capture_sql_exception.<locals>.deco(a, kw) 167 def deco(*a: Any, *kw: Any) -> Any: 168 try: --> 169 return f(a, kw) 170 except Py4JJavaError as e: 171 converted = convert_exception(e.java_exception) File ~/cluster-env/env/lib/python3.10/site-packages/py4j/protocol.py:326, in get_return_value(answer, gateway_client, target_id, name) 324 value = OUTPUT_CONVERTER[type] 325 if answer[1] == REFERENCE_TYPE: --> 326 raise Py4JJavaError( 327 "An error occurred while calling {0}{1}{2}.\n". 328 format(target_id, ".", name), value) 329 else: 330 raise Py4JError( 331 "An error occurred while calling {0}{1}{2}. Trace:\n{3}\n". 332 format(target_id, ".", name, value)) Py4JJavaError: An error occurred while calling z:com.microsoft.azure.synapse.tokenlibrary.TokenLibrary.getConnectionString. : com.microsoft.azure.synapse.tokenlibrary.TokenServiceClientResponseStatusException: Token Service returned 'Client Error' (400), with message: {"result":"DependencyError","errorId":"BadRequest","errorMessage":"[Code=400, Target=gApachub_TST, Message=Missing required property 'connectionstring' on gApachub_TST, ExceptionDetail=]. TraceId : d81524ca-38c3-4a3f-ac3c-99c53340f5e4 | client-request-id : 18a72203-4acc-4bc7-9a96-11db3eeecc99. Error Component : LSR"} at com.microsoft.azure.synapse.tokenlibrary.TokenServiceClient.invokePostApi(TokenServiceClient.scala:146) at com.microsoft.azure.synapse.tokenlibrary.TokenServiceClient.callLinkedServiceApi(TokenServiceClient.scala:178) at com.microsoft.azure.synapse.tokenlibrary.TokenLibraryInternal.tokenServiceCall(TokenLibraryInternal.scala:118) at com.microsoft.azure.synapse.tokenlibrary.TokenLibraryInternal.$anonfun$getAccessToken$4(TokenLibraryInternal.scala:184) at com.microsoft.azure.synapse.tokenlibrary.TokenLibraryInternal.checkCacheAndCall$1(TokenLibraryInternal.scala:64) at com.microsoft.azure.synapse.tokenlibrary.TokenLibraryInternal.getAccessTokenFromCacheOrCallTokenService(TokenLibraryInternal.scala:74) at com.microsoft.azure.synapse.tokenlibrary.TokenLibraryInternal.getAccessToken(TokenLibraryInternal.scala:184) at com.microsoft.azure.synapse.tokenlibrary.TokenLibraryInternal.getLinkedServiceAccessToken(TokenLibraryInternal.scala:193) at com.microsoft.azure.synapse.tokenlibrary.TokenLibrary$.getConnectionString(TokenLibrary.scala:240) at com.microsoft.azure.synapse.tokenlibrary.TokenLibrary.getConnectionString(TokenLibrary.scala) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244) at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:374) at py4j.Gateway.invoke(Gateway.java:282) at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132) at py4j.commands.CallCommand.execute(CallCommand.java:79) at py4j.GatewayConnection.run(GatewayConnection.java:238) at java.base/java.lang.Thread.run(Thread.java:829)

phemanth 15,755 Microsoft External Staff Moderator

@Lotus88

Thank you for sharing the error details. It looks like the issue might be related to the configuration of the linked service in version 2.0. Here are a few steps and an example to help you set up and use linked service v2.0 in your Synapse notebook:

Check Linked Service Configuration: Ensure that the linked service gApachub_TST is correctly configured in your Synapse workspace. Verify that all required properties, including the connection string, are set.

Authentication: Make sure that the authentication method used in v2.0 is correctly set up. This might involve updating tokens or API keys.

Example Code: Here is an example of how to use a linked service in a Synapse notebook:

   from azure.synapse.artifacts import ArtifactsClient
   from azure.identity import DefaultAzureCredential
   # Replace with your Synapse workspace details
   workspace_endpoint = 'https://.dev.azuresynapse.net'
   linked_service_name = 'gApachub_TST'
   # Authenticate and create a client
   credential = DefaultAzureCredential()
   synapse_client = ArtifactsClient(endpoint=workspace_endpoint, credential=credential)
   # Get the linked service
   linked_service = synapse_client.get_linked_service(linked_service_name=linked_service_name)
   # Extract the connection string
   connection_string = linked_service.properties['connectionString']
   # Use the connection string in your JDBC URL
   jdbc_url_db1 = f"jdbc:sqlserver://{serverNameSrc}:{portNum};database={databaseSrc};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"
   # Set up connection properties
   conn_properties_db1 = {
       "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
       "accessToken": connection_string
   }
   print(conn_properties_db1)

Lotus88 176 Reputation points

2025-05-30T06:58:15.5666667+00:00

Hi @phemanth

I tested the linked service and the connection is ok.

I got this error. Do I need to install anything?

ModuleNotFoundError: No module named 'azure.synapse.artifacts'

phemanth 15,755 Microsoft External Staff Moderator

@Lotus88

It looks like you need to install the azure-synapse-artifacts module to resolve the ModuleNotFoundError. You can install it using pip. Here's how you can do it:

Installation Steps

Open your terminal or command prompt.
Run the following command:

pip install azure-synapse-artifacts

Example Code

After installing the module, you can use the following example code to set up and use the linked service in your Synapse notebook:

from azure.synapse.artifacts import ArtifactsClient
from azure.identity import DefaultAzureCredential
# Replace with your Synapse workspace details
workspace_endpoint = 'https://.dev.azuresynapse.net'
linked_service_name = 'gApachub_TST'
# Authenticate and create a client
credential = DefaultAzureCredential()
synapse_client = ArtifactsClient(endpoint=workspace_endpoint, credential=credential)
# Get the linked service
linked_service = synapse_client.get_linked_service(linked_service_name=linked_service_name)
# Extract the connection string
connection_string = linked_service.properties['connectionString']
# Use the connection string in your JDBC URL
jdbc_url_db1 = f"jdbc:sqlserver://{serverNameSrc}:{portNum};database={databaseSrc};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"
# Set up connection properties
conn_properties_db1 = {
    "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
    "accessToken": connection_string
}
print(conn_properties_db1)

For more details, you can refer to the official documentation

.

Lotus88 176 Reputation points

2025-06-05T01:26:54.5466667+00:00

Hi @phemanth

For your code above, it is applicable for recommended or legacy linked service?
Lotus88 176 Reputation points

2025-06-12T10:02:39.9066667+00:00

Hi @phemanth

I tried the python code that you provided but I encountered this error. I am using Linked service v2. May I know what would be the problem? Thanks in advance.
J N S S Kasyap 3,625 Reputation points Microsoft External Staff Moderator

2025-06-17T09:51:18.4266667+00:00

@Lotus88
Linked Service v2.0 is not currently supported inside Synapse Notebooks using TokenLibrary.getConnectionString(). This method only works with Linked Service v1.0 (Recommended or Legacy), which exposes a connection string property.
If you're using v2.0 (which includes managed identities and private endpoints), the connection string is not available and leads to the Missing required property 'connectionstring' error. For notebooks, stick to Linked Service v1.0 or use DefaultAzureCredential to manually authenticate against Azure SQL.
Lotus88 176 Reputation points

2025-06-18T01:32:43.96+00:00

Hi @J N S S Kasyap

I have tried DefaultAzureCredential but it is not working. My boss don't allow me to use Linked Service v1.0 as it is legacy. He probably worry about security issue and in long run, it this is removed, we still need to find another solution.
J N S S Kasyap 3,625 Reputation points Microsoft External Staff Moderator

2025-06-18T09:32:18.53+00:00
@Lotus88
Instead of relying on Linked Service v1.0, the production-grade approach is to securely connect to Azure SQL by generating an access token using ManagedIdentityCredential or AzureCliCredential, and then using that token in your JDBC connection. This method aligns with long-term security best practices, avoids hardcoding credentials, and ensures compatibility even if Linked Service v1.0 is deprecated in the future.

Using Azure Managed Identity with token-based JDBC authentication is a secure and future-proof approach that aligns with Microsoft’s best practices. It eliminates the need for hardcoded secrets and avoids reliance on deprecated linked service versions, ensuring better security and maintainability for enterprise-grade data solutions.
Prerequisites:

Enable System-Assigned Managed Identity for your Synapse workspace.

Grant that identity access to your Azure SQL DB:

CREATE USER [<workspace-managed-identity-name>] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [<workspace-managed-identity-name>];

Ensure your SQL firewall allows the Managed VNet

from azure.identity import ManagedIdentityCredential # Step 1: Get access token for Azure SQL credential = ManagedIdentityCredential() token = credential.get_token("https://database.windows.net/").token # Step 2: Build JDBC connection jdbc_url = "jdbc:sqlserver://<your-server>.database.windows.net:1433;" \ "database=<your-database>;encrypt=true;" \ "trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;" # Step 3: Use token in Spark read df = spark.read \ .format("jdbc") \ .option("url", jdbc_url) \ .option("accessToken", token) \ .option("dbtable", "<your-table-name>") \ .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver") \ .load() df.show()

Use ManagedIdentityCredential() to fetch a secure token, and connect to Azure SQL via JDBC using that token. This is secure, supported, and avoids reliance on Linked Service v1.0 or TokenLibrary.
Lotus88 176 Reputation points

2025-06-18T10:07:41.96+00:00

Hi J N S S Kasyap,

I will try your method.

I am currently looking at Key vault but I don't feel that secure too as should be able to print the password in notebook.

Thank you!
Lotus88 176 Reputation points

2025-06-20T10:01:33.76+00:00

Hi @J N S S Kasyap ,

I tried your method but I got error.

Below is my code. The DB is also granted access to workspace. How can I check if the System-Assigned Managed Identity is enabled to my workspace? Do you have screenshot how does it look like ? I will to search online but seems no clear direction. Thanks in advance.
Lotus88 176 Reputation points

2025-06-24T02:57:11.9266667+00:00

Hi @J N S S Kasyap , is there anything I miss out when assigned managed identity? my Linked service can assigned to system managed identity so I guess it is enabled for synapse workspace.
J N S S Kasyap 3,625 Reputation points Microsoft External Staff Moderator

2025-06-25T12:24:59.8666667+00:00
@Lotus88
The error you're facing usually means the System-Assigned Managed Identity (MSI) isn't enabled at the Synapse workspace level, even if it's set in the Linked Service. Please go to your Synapse workspace in the Azure portal → Identity → System Assigned → turn it On and Save.

Also ensure that the workspace MSI has been added to your Azure SQL Database via:

CREATE USER [<workspacename>] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [<workspacename>];

Once this is done, retry your notebook. This approach is secure and avoids reliance on legacy Linked Services or hardcoded credentials.
Lotus88 176 Reputation points

2025-06-27T02:44:09.1566667+00:00

Hi,

This is my synapse workspace and from the Identity, I did not saw any option that allow me to enable the System Assigned. It is enabled by default when the workspace is created ? Do I need to assign any role to it ?

1 answer

Your answer

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2025-05-28T05:50:03.38+00:00

@Lotus88

It seems like you're having a bit of trouble while switching to linked service v2.0 in your Synapse notebook. Let’s troubleshoot this together.

When you change to version 2, there could be several reasons why you're encountering errors. Here are a few things you might want to check:

Configuration Settings: Make sure you configure any necessary parameters specific to linked service v2.0. There might be settings that were automatically managed in v1 but now need to be input manually in v2.

Authentication: Check if the authentication method you are using for the linked service is still valid in v2. Any API or token updates needed for v2 should be applied.

Cluster Settings: Sometimes linked services require specific cluster configurations or dependencies. Ensure that the cluster settings align with what v2 needs.

Error Details: If you can, provide more details on the error message you're seeing when trying to use v2. Specific error codes or messages can help pinpoint the issue more accurately.

Review the official documentation related to the linked service v2.0 to see if there are any additional setup steps you may have overlooked.

Hope this helps. Do let us know if you any further queries.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2025-05-30T05:15:52.43+00:00

@Lotus88

Thank you for sharing the error details. It looks like the issue might be related to the configuration of the linked service in version 2.0. Here are a few steps and an example to help you set up and use linked service v2.0 in your Synapse notebook:

Check Linked Service Configuration: Ensure that the linked service gApachub_TST is correctly configured in your Synapse workspace. Verify that all required properties, including the connection string, are set.

Authentication: Make sure that the authentication method used in v2.0 is correctly set up. This might involve updating tokens or API keys.

Example Code: Here is an example of how to use a linked service in a Synapse notebook:
from azure.synapse.artifacts import ArtifactsClient from azure.identity import DefaultAzureCredential # Replace with your Synapse workspace details workspace_endpoint = 'https://.dev.azuresynapse.net' linked_service_name = 'gApachub_TST' # Authenticate and create a client credential = DefaultAzureCredential() synapse_client = ArtifactsClient(endpoint=workspace_endpoint, credential=credential) # Get the linked service linked_service = synapse_client.get_linked_service(linked_service_name=linked_service_name) # Extract the connection string connection_string = linked_service.properties['connectionString'] # Use the connection string in your JDBC URL jdbc_url_db1 = f"jdbc:sqlserver://{serverNameSrc}:{portNum};database={databaseSrc};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30" # Set up connection properties conn_properties_db1 = { "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver", "accessToken": connection_string } print(conn_properties_db1)
Lotus88 176 Reputation points

2025-05-30T06:58:15.5666667+00:00

Hi @phemanth

I tested the linked service and the connection is ok.

I got this error. Do I need to install anything?

ModuleNotFoundError: No module named 'azure.synapse.artifacts'
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2025-06-04T19:09:30.32+00:00

@Lotus88

It looks like you need to install the azure-synapse-artifacts module to resolve the ModuleNotFoundError. You can install it using pip. Here's how you can do it:

Installation Steps

Open your terminal or command prompt.

Run the following command:

pip install azure-synapse-artifacts

Example Code

After installing the module, you can use the following example code to set up and use the linked service in your Synapse notebook:

from azure.synapse.artifacts import ArtifactsClient from azure.identity import DefaultAzureCredential # Replace with your Synapse workspace details workspace_endpoint = 'https://.dev.azuresynapse.net' linked_service_name = 'gApachub_TST' # Authenticate and create a client credential = DefaultAzureCredential() synapse_client = ArtifactsClient(endpoint=workspace_endpoint, credential=credential) # Get the linked service linked_service = synapse_client.get_linked_service(linked_service_name=linked_service_name) # Extract the connection string connection_string = linked_service.properties['connectionString'] # Use the connection string in your JDBC URL jdbc_url_db1 = f"jdbc:sqlserver://{serverNameSrc}:{portNum};database={databaseSrc};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30" # Set up connection properties conn_properties_db1 = { "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver", "accessToken": connection_string } print(conn_properties_db1)

For more details, you can refer to the official documentation

.
Lotus88 176 Reputation points

2025-06-05T01:26:54.5466667+00:00

Hi @phemanth

For your code above, it is applicable for recommended or legacy linked service?
Lotus88 176 Reputation points

2025-06-12T10:02:39.9066667+00:00

Hi @phemanth

I tried the python code that you provided but I encountered this error. I am using Linked service v2. May I know what would be the problem? Thanks in advance.
J N S S Kasyap 3,625 Reputation points Microsoft External Staff Moderator

2025-06-17T09:51:18.4266667+00:00

@Lotus88
Linked Service v2.0 is not currently supported inside Synapse Notebooks using TokenLibrary.getConnectionString(). This method only works with Linked Service v1.0 (Recommended or Legacy), which exposes a connection string property.
If you're using v2.0 (which includes managed identities and private endpoints), the connection string is not available and leads to the Missing required property 'connectionstring' error. For notebooks, stick to Linked Service v1.0 or use DefaultAzureCredential to manually authenticate against Azure SQL.
Lotus88 176 Reputation points

2025-06-18T01:32:43.96+00:00

Hi @J N S S Kasyap

I have tried DefaultAzureCredential but it is not working. My boss don't allow me to use Linked Service v1.0 as it is legacy. He probably worry about security issue and in long run, it this is removed, we still need to find another solution.
Lotus88 176 Reputation points

2025-06-18T10:07:41.96+00:00

Hi J N S S Kasyap,

I will try your method.

I am currently looking at Key vault but I don't feel that secure too as should be able to print the password in notebook.

Thank you!
Lotus88 176 Reputation points

2025-06-20T10:01:33.76+00:00

Hi @J N S S Kasyap ,

I tried your method but I got error.

Below is my code. The DB is also granted access to workspace. How can I check if the System-Assigned Managed Identity is enabled to my workspace? Do you have screenshot how does it look like ? I will to search online but seems no clear direction. Thanks in advance.
Lotus88 176 Reputation points

2025-06-24T02:57:11.9266667+00:00

Hi @J N S S Kasyap , is there anything I miss out when assigned managed identity? my Linked service can assigned to system managed identity so I guess it is enabled for synapse workspace.
J N S S Kasyap 3,625 Reputation points Microsoft External Staff Moderator

2025-06-25T12:24:59.8666667+00:00

@Lotus88
The error you're facing usually means the System-Assigned Managed Identity (MSI) isn't enabled at the Synapse workspace level, even if it's set in the Linked Service. Please go to your Synapse workspace in the Azure portal → Identity → System Assigned → turn it On and Save.

Also ensure that the workspace MSI has been added to your Azure SQL Database via:

CREATE USER [<workspacename>] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [<workspacename>];

Once this is done, retry your notebook. This approach is secure and avoids reliance on legacy Linked Services or hardcoded credentials.
Lotus88 176 Reputation points

2025-06-27T02:44:09.1566667+00:00

Hi,

This is my synapse workspace and from the Identity, I did not saw any option that allow me to enable the System Assigned. It is enabled by default when the workspace is created ? Do I need to assign any role to it ?

Answer 1

Bajura, Keith 0

I don't think this code is correct. I was able to fix the "ArtifactsClient" object error above by adding "

linked_service = synapse_client.linked_service.get_linked_service(linked_service_name=linked_service_names)

But now I am getting "DefaultAzureCredential failed to retrieve a token from the included credentials."

Share via

Issue using linked service v 2.0 on notebook

1 answer

Your answer