PowerShell and AlwaysOn - Gotcha - Exception setting "ConnectionString": "Keyword not supported: 'applicationintent'."

Published Jan 15 2019 02:35 PM 345 Views
Microsoft
First published on MSDN on Aug 09, 2012
Here is the an issue I saw come across an alias that is a gotcha!

I’m running into a problem connecting to an AlwaysOn read-intent secondary and I was wondering if someone could help me out.  I have the .Net Framework 4.5 installed and the newest SQL Client install for SQL Server 2012.  Running this command from the server where SQL Server 2012 is installed works fine:

$conn=new-object System.Data.SqlClient.SQLConnection

$ConnectionString = "Server=tcp:AGL,1800;Database=Contoso;Integrated Security=SSPI"

$conn.ConnectionString=$ConnectionString

$conn.Open()

However, as soon as I include the ApplicationIntent=ReadOnly switch, it fails.  I’m not sure how to specify that the SQL Native Client 11 should be used in the connection string (and not sure if I need to do such a thing.

$conn=new-object System.Data.SqlClient.SQLConnection

$ConnectionString = "Server=tcp:AGL,1800;Database=Contoso;Integrated Security=SSPI;ApplicationIntent=ReadOnly"

$conn.ConnectionString=$ConnectionString

$conn.Open()

Here is the error I receive, which is odd.  The casing in the error message is completely different than I specify.  Must be hard-coded somewhere.

Exception setting "ConnectionString": "Keyword not supported: 'applicationintent'."

At line:3 char:7

+ $conn. <<<< ConnectionString=$ConnectionString

+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException

+ FullyQualifiedErrorId : PropertyAssignmentException"

------------------------------------------------------------

Answer: You have to use a version of PowerShell that loads the correct framework.

You can execute the following PowerShell command to interrogate the version of CLR loaded: $PSVersionTable.CLRVersion

You can either:

Bob Dorr - Principal SQL Server Escalation Engineer

%3CLINGO-SUB%20id%3D%22lingo-sub-317166%22%20slang%3D%22en-US%22%3EPowerShell%20and%20AlwaysOn%20-%20Gotcha%20-%20Exception%20setting%20%22ConnectionString%22%3A%20%22Keyword%20not%20supported%3A%20'applicationintent'.%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-317166%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Aug%2009%2C%202012%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20Here%20is%20the%20an%20issue%20I%20saw%20come%20across%20an%20alias%20that%20is%20a%20gotcha!%3C%2FSTRONG%3E%3CP%3E%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%0A%20%20%20%3CP%3E%3C%2FP%3EI%E2%80%99m%20running%20into%20a%20problem%20connecting%20to%20an%20AlwaysOn%20read-intent%20secondary%20and%20I%20was%20wondering%20if%20someone%20could%20help%20me%20out.%26nbsp%3B%20I%20have%20the%20.Net%20Framework%204.5%20installed%20and%20the%20newest%20SQL%20Client%20install%20for%20SQL%20Server%202012.%26nbsp%3B%20Running%20this%20command%20from%20the%20server%20where%20SQL%20Server%202012%20is%20installed%20works%20fine%3A%3CP%3E%3C%2FP%3E%0A%20%20%20%3CTABLE%3E%0A%20%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%20%3CTD%3E%3CP%3E%24conn%3Dnew-object%20System.Data.SqlClient.SQLConnection%3C%2FP%3E%0A%20%20%20%20%20%20%3CP%3E%24ConnectionString%20%3D%20%22Server%3Dtcp%3AAGL%2C1800%3BDatabase%3DContoso%3BIntegrated%20Security%3DSSPI%22%3C%2FP%3E%0A%20%20%20%20%20%20%3CP%3E%24conn.ConnectionString%3D%24ConnectionString%3C%2FP%3E%0A%20%20%20%20%20%20%3CP%3E%24conn.Open()%3C%2FP%3E%0A%20%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%0A%20%20%20%3CP%3EHowever%2C%20as%20soon%20as%20I%20include%20the%20ApplicationIntent%3DReadOnly%20switch%2C%20it%20fails.%26nbsp%3B%20I%E2%80%99m%20not%20sure%20how%20to%20specify%20that%20the%20SQL%20Native%20Client%2011%20should%20be%20used%20in%20the%20connection%20string%20(and%20not%20sure%20if%20I%20need%20to%20do%20such%20a%20thing.%3C%2FP%3E%0A%20%20%20%3CP%3E%3C%2FP%3E%0A%20%20%20%3CP%3E%3C%2FP%3E%0A%20%20%20%3CP%3E%3C%2FP%3E%0A%20%20%20%3CP%3E%3C%2FP%3E%0A%20%20%20%3CTABLE%3E%0A%20%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%20%3CTD%3E%3CP%3E%24conn%3Dnew-object%20System.Data.SqlClient.SQLConnection%3C%2FP%3E%0A%20%20%20%20%20%20%3CP%3E%24ConnectionString%20%3D%20%22Server%3Dtcp%3AAGL%2C1800%3BDatabase%3DContoso%3BIntegrated%20Security%3DSSPI%3BApplicationIntent%3DReadOnly%22%3C%2FP%3E%0A%20%20%20%20%20%20%3CP%3E%24conn.ConnectionString%3D%24ConnectionString%3C%2FP%3E%0A%20%20%20%20%20%20%3CP%3E%24conn.Open()%3C%2FP%3E%0A%20%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%0A%20%20%20%3CP%3EHere%20is%20the%20error%20I%20receive%2C%20which%20is%20odd.%26nbsp%3B%20The%20casing%20in%20the%20error%20message%20is%20completely%20different%20than%20I%20specify.%26nbsp%3B%20Must%20be%20hard-coded%20somewhere.%3C%2FP%3E%0A%20%20%20%3CP%3E%3C%2FP%3E%0A%20%20%20%3CP%3E%3C%2FP%3E%0A%20%20%20%3CTABLE%3E%0A%20%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%20%3CTD%3EException%20setting%20%22ConnectionString%22%3A%20%22Keyword%20not%20supported%3A%20'applicationintent'.%22%3CP%3EAt%20line%3A3%20char%3A7%3C%2FP%3E%0A%20%20%20%20%20%20%3CP%3E%2B%20%24conn.%20%26lt%3B%26lt%3B%26lt%3B%26lt%3B%20ConnectionString%3D%24ConnectionString%3C%2FP%3E%0A%20%20%20%20%20%20%3CP%3E%2B%20CategoryInfo%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3A%20InvalidOperation%3A%20(%3A)%20%5B%5D%2C%20RuntimeException%3C%2FP%3E%0A%20%20%20%20%20%20%3CP%3E%2B%20FullyQualifiedErrorId%20%3A%20PropertyAssignmentException%22%3C%2FP%3E%0A%20%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%0A%20%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E------------------------------------------------------------%3C%2FP%3E%0A%20%20%3CP%3E%3CSTRONG%3EAnswer%3A%20%3C%2FSTRONG%3E%20You%20have%20to%20use%20a%20version%20of%20PowerShell%20that%20loads%20the%20correct%20framework.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%3CTD%3EYou%20can%20execute%20the%20following%20PowerShell%20command%20to%20interrogate%20the%20version%20of%20CLR%20loaded%3A%20%3CEM%3E%20%24PSVersionTable.CLRVersion%3C%2FEM%3E%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%20%3CTR%3E%0A%20%20%20%20%3CTD%3E%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%20%3CTR%3E%0A%20%20%20%20%3CTD%3E%3CP%3EYou%20can%20either%3A%3C%2FP%3E%0A%20%20%20%20%20%3CP%3E%3C%2FP%3E%0A%20%20%20%20%20%3CUL%3E%0A%20%20%20%20%20%20%3CLI%3E%3CDIV%3EUse%20a%20version%20of%20PowerShell%20that%20loads%20the%204.x%20framework%3C%2FDIV%3E%0A%20%20%20%20%20%20%3C%2FLI%3E%0A%20%20%20%20%20%20%3CLI%3E%3CDIV%3EUse%20a%20version%20of%20the%20.NET%202.x%20(3.5)%20framework%20that%20supports%20the%20ApplicationIntent%20attribute%20-%20%3CA%20href%3D%22http%3A%2F%2Fsupport.microsoft.com%2Fkb%2F2654347%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20http%3A%2F%2Fsupport.microsoft.com%2Fkb%2F2654347%3C%2FA%3E%3C%2FDIV%3E%0A%20%20%20%20%20%20%3C%2FLI%3E%0A%20%20%20%20%20%3C%2FUL%3E%0A%20%20%20%20%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%0A%20%20%3CP%3E%3CEM%3E%20%3C%2FEM%3E%3C%2FP%3E%0A%20%20%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%3CTD%3E%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EBob%20Dorr%20-%20Principal%20SQL%20Server%20Escalation%20Engineer%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-317166%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Aug%2009%2C%202012%20Here%20is%20the%20an%20issue%20I%20saw%20come%20across%20an%20alias%20that%20is%20a%20gotcha!%20I%E2%80%99m%20running%20into%20a%20problem%20connecting%20to%20an%20AlwaysOn%20read-intent%20secondary%20and%20I%20was%20wondering%20if%20someone%20could%20help%20me%20out.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-317166%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EHigh%20Availability%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 02:35 PM
Updated by: