locked
Unable to create Azure SQL DB Linked service using powershell RRS feed

  • Question

  • Hi All,

    I am trying to create Azure SQL DB Linked service using PowerShell  but it is getting failed, don't know the exact reason

    below is the code which I am using to <g class="gr_ gr_25 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" data-gr-id="25" id="25">create .</g>

    I have one Jason file which consists below code

    {
        "name": "AzureSQL_LS",
        "properties": {
            "type": "AzureSqlDatabase",
            "typeProperties": {
                "connectionString": "Server=<servername>.database.windows.net,1433;Database=serverertestdb;User ID=importnat@servertest1234;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
            }
        }


    }

    I am using below command to create linked service

    Set-AzureRmDataFactoryV2LinkedService -DataFactoryName "adfpredmentv" -ResourceGroupName "servicetestRG" -Name "AzureSQL_LS" -DefinitionFile "D:\data-factory-predictive\linkedService\AzureSQL_LS.json"

    ERROR

    Set-AzureRmDataFactoryV2LinkedService : Json is not valid. Details: 'Newtonsoft.Json.JsonReaderException: Error re
    is not an object: String. Path '', line 6, position 235.
       at Newtonsoft.Json.Linq.JObject.Load(JsonReader reader)
       at Microsoft.Rest.Serialization.PolymorphicDeserializeJsonConverter`1.ReadJson(JsonReader reader, Type objectTy
       at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.DeserializeConvertable(JsonConverter converter, J
       at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.Deserialize(JsonReader reader, Type objectType, B
       at Newtonsoft.Json.Linq.JToken.ToObject(Type objectType, JsonSerializer jsonSerializer)
       at Microsoft.Rest.Serialization.PolymorphicDeserializeJsonConverter`1.ReadJson(JsonReader reader, Type objectTy
       at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.DeserializeConvertable(JsonConverter converter, J
       at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.SetPropertyValue(JsonProperty property, JsonConve
    containerContract, JsonProperty containerProperty, JsonReader reader, Object target)
       at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateObject(Object newObject, JsonReader reade
    String id)
       at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType,
    JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
       at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.Deserialize(JsonReader reader, Type objectType, B
       at Newtonsoft.Json.JsonSerializer.DeserializeInternal(JsonReader reader, Type objectType)
       at Microsoft.Rest.Serialization.SafeJsonConvert.DeserializeObject[T](String json, JsonSerializerSettings settin
       at Microsoft.Azure.Commands.DataFactoryV2.DataFactoryClient.CreateOrUpdateLinkedService(String resourceGroupNam
    String rawJsonContent)'
    At line:1 char:1
    + Set-AzureRmDataFactoryV2LinkedService -DataFactoryName "adfpredmentv" ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : CloseError: (:) [Set-AzureRmDataFactoryV2LinkedService], ArgumentException
        + FullyQualifiedErrorId : Microsoft.Azure.Commands.DataFactoryV2.SetAzureDataFactoryLinkedServiceCommand

    Please help me to resiolve this issue


    Thankx &amp; regards, Vipin jha MCP




    • Edited by Vipin jha Tuesday, September 25, 2018 3:57 PM error
    • Edited by Jason_J (Azure) Tuesday, September 25, 2018 5:31 PM credentials masked
    Tuesday, September 25, 2018 1:54 PM

Answers

  • Hello,

    I was not able to successfully create the linked service using the JSON code format you provided. 

    I was able to do it with this format:

    {
    	"name": "AzureSQL_LS",
    	"properties": {
    		"type": "AzureSqlDatabase",
    		"typeProperties": {
    			"connectionString": {
    				"type": "SecureString",
    				"value": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;User ID=<username>;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
    			}
    		}
    	}
    }

    Your Powershell command looks correct, just change your JSON file to match the above format. :)

    • Proposed as answer by Jason_J (Azure) Tuesday, September 25, 2018 9:03 PM
    • Marked as answer by Vipin jha Sunday, September 30, 2018 4:14 PM
    Tuesday, September 25, 2018 9:02 PM

All replies

  • Hello,

    I was not able to successfully create the linked service using the JSON code format you provided. 

    I was able to do it with this format:

    {
    	"name": "AzureSQL_LS",
    	"properties": {
    		"type": "AzureSqlDatabase",
    		"typeProperties": {
    			"connectionString": {
    				"type": "SecureString",
    				"value": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;User ID=<username>;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
    			}
    		}
    	}
    }

    Your Powershell command looks correct, just change your JSON file to match the above format. :)

    • Proposed as answer by Jason_J (Azure) Tuesday, September 25, 2018 9:03 PM
    • Marked as answer by Vipin jha Sunday, September 30, 2018 4:14 PM
    Tuesday, September 25, 2018 9:02 PM
  • Thanks, Jason, this code worked for me.

    Thank you again for your support


    Thankx &amp; regards, Vipin jha MCP

    Sunday, September 30, 2018 4:14 PM