Hi , how we can add link services created in synapse in notebook available azure synapse link servicename :test2 select * from test1.table1

synapse notebook - Microsoft Q&A

phemanth 8,645 Microsoft Vendor

@Vineet S

Thanks for reaching out to Microsoft Q&A.

method 1

Verify Managed Identity:
- Ensure that your Azure Synapse workspace has a managed identity enabled. You can check this in the Azure portal under “Workspace settings” -> “Identity.”
- If it’s not enabled, create one for your Synapse workspace.
Reference Linked Service in SQL:
- In your Synapse notebook, write a SQL query that references the linked service using the fully qualified object name (FQON) format.
- Here’s an example query:SQL
```
     SELECT * FROM test2.test1.table1;
```
  Replace test2 with the actual name of your linked service.
- Make sure that the linked service (test2 in this case) has the necessary permissions to access the target table (test1.table1).

method 2

To use a linked service in an Azure Synapse notebook, you can follow these steps:

Initialize a Spark session. This can be done using the following code: Python

   from pyspark.sql import SparkSession
   spark = SparkSession.builder.getOrCreate()

Configure Synapse’s OAuth provider to authorize the linked service. It uses whatever authentication method you set up in your linked service configuration. Replace LinkedServiceName with the name of your linked service (in your case, it’s test2):
```
   spark.conf.set("spark.storage.synapse.linkedServiceName", "test2")
   spark.conf.set("fs.azure.account.oauth.provider.type", "com.microsoft.azure.synapse.tokenlibrary.LinkedServiceBasedTokenProvider")
```
Specify the URL for the linked service. Replace <container-name> and <storage-account-name> with your specific details:
```
   base_url = "abfss://<container-name>@<storage-account-name>.dfs.core.windows.net"
   
```
Read data from a table. Here’s an example of how to read data from a Parquet file. You can replace example.parquet with your specific file path:
```
   df_parquet = spark.read.parquet(f"{base_url}/some-folder/example.parquet")
```

for more details refer https://segunakinyemi.com/blog/reference-linked-services-synapse/

In your case, if you want to select all data from test1.table1, you can replace example.parquet with test1.table1.

Please note that you need to replace <container-name> and <storage-account-name> with your specific details. Also, make sure that the linked service test2 has the necessary permissions to access test1.table1.

Hope this helps. Do let us know if you any further queries

Vineet S 445

how about sql server query

Vineet S 445

from pyspark.sql import SparkSession

# Initialize Spark session.
spark = SparkSession.builder.getOrCreate()

# Configure Synapse's OAuth provider to authorize the linked service. It uses whatever authentication method you set up in your linked service configuration.
spark.conf.set("spark.storage.synapse.linkedServiceName", "LinkedServiceName")
spark.conf.set("fs.azure.account.oauth.provider.type", "com.microsoft.azure.synapse.tokenlibrary.LinkedServiceBasedTokenProvider")


LinkedServiceBasedTokenProvider?? values
how to query table in db
how to connect using service principle

phemanth 8,645 Microsoft Vendor

@Vineet S

Querying SQL Server Tables in Azure Synapse Notebooks using Service PrincipalHere's a comprehensive guide on querying SQL Server tables within Azure Synapse notebooks leveraging service principal authentication:

Prerequisites:

Azure Synapse Workspace with Managed Identity: Ensure your workspace has a managed identity enabled. You can check and create one in the Azure portal under "Workspace settings" -> "Identity."
Service Principal Creation: Create a service principal in Azure Active Directory (AAD) with the necessary permissions to access the SQL Server database. Refer to Microsoft's documentation for detailed steps: https://learn.microsoft.com/en-us/entra/identity-platform/howto-create-service-principal-portal
Linked Service Configuration: Create a linked service in your Azure Synapse workspace that points to the SQL Server database. Configure the service principal authentication method within the linked service, specifying the application ID, client secret, and resource URI (SQL Server endpoint).
SQL Server Permissions: Grant the service principal appropriate permissions (e.g., SELECT) on the specific table(s) you want to query.

Steps:

Import Libraries: In your Synapse notebook, import the necessary libraries:
```
   import pyodbc
   from pyspark.sql import SparkSession
```
Create Spark Session (Optional): If using Spark for data processing, create a Spark session:
```
   spark = SparkSession.builder.getOrCreate()
```

Connect to SQL Server using Service Principal: Establish a connection to the SQL Server database using the linked service credentials: Python

   # Retrieve linked service information from Synapse configurations (replace with your values)
   linked_service_name = "your_linked_service_name"
   # Assuming you have retrieved credentials securely (avoid hardcoding)
   application_id = "your_service_principal_application_id"
   client_secret = "your_service_principal_client_secret"
   resource_uri = "your_sql_server_endpoint_uri"
   # Construct the connection string with service principal authentication details
   conn_string = f"""DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={linked_service_name}.database.windows.net;DATABASE={linked_service_name};Authentication=Active Directory Interactive;UID={application_id};PWD={client_secret};{resource_uri}"""
   # Create a connection using pyodbc
   conn = pyodbc.connect(conn_string)
   # Create a cursor for executing queries
   cursor = conn.cursor()

Security Considerations:

Avoid hardcoding credentials in your code. Consider using environment variables or secure storage mechanisms to store sensitive information.
Grant the service principal least privilege permissions to minimize security risks.

Execute SQL Query: Run the desired SQL query using the cursor.execute() method:

   query = "SELECT * FROM dbo.your_table_name"
   cursor.execute(query)
   # Retrieve results
   results = cursor.fetchall()
   # Process results (e.g., print or store in a DataFrame)
   for row in results:
       print(row)  # Example: print each row
       # Alternatively, create a Spark DataFrame for further processing
       # if using Spark:
       #     df = spark.createDataFrame(results, schema=["column1", "column2", ...])
       #     # Perform DataFrame operations...

Close Connections: After processing, close the cursor and connection to release resources:
```
   cursor.close()
   conn.close()
   
```
Replace placeholders with your actual values (linked service name, credentials, etc.).
For Spark processing, you can create a Spark DataFrame from the retrieved results using spark.createDataFrame().
Ensure your pyodbc library is installed (pip install pyodbc).Querying SQL Server Tables in Azure Synapse Notebooks using Service Principal

phemanth 8,645 Microsoft Vendor

@Vineet S We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

phemanth 8,645 Microsoft Vendor

@Vineet S We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

synapse notebook