Consuming JSON Formatted API Data in 2016

  • Velveeta22

    Ten Centuries

    Points: 1289

    Comments posted to this topic are about the item Consuming JSON Formatted API Data in 2016

  • fregatepllada

    SSCommitted

    Points: 1648

    I quite disagree that SQL Server nvarchar data type is limited by 4,000 bytes 😉

    nvarchar [ ( n | max ) ]

    Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes.

  • Bosko Vukov

    Old Hand

    Points: 339

    Hi, the problem is not in the nvarchar type but in the limitation of sp_OAGetProperty in the line:

    EXEC @hr = sp_OAGetProperty @Obj, 'ResponseText', @json OUTPUT

  • fregatepllada

    SSCommitted

    Points: 1648

    It's amazing that usage of CLR is no-no in SQL Server , but old-fashion OLE/COM integration is acceptable 😉

  • Bosko Vukov

    Old Hand

    Points: 339

    fregatepllada (6/2/2016)


    It's amazing that usage of CLR is no-no in SQL Server , but old-fashion OLE/COM integration is acceptable 😉

    ... and you can load any COM junk (with memory leaks and other bugs) inside SQL Server process and destroy it much easier then with SAFE (or EXTERNAL ACCESS) CLR.

  • fregatepllada

    SSCommitted

    Points: 1648

    How to build a HELL in each SQL Server :angry:

  • Velveeta22

    Ten Centuries

    Points: 1289

    Checking on sp_OA limits, probably the character limit issue.

  • Velveeta22

    Ten Centuries

    Points: 1289

    fregatepllada (6/2/2016)


    How to build a HELL in each SQL Server :angry:

    Angry face is confusing. Please expand on the issue.

  • Robert Sterbal

    SSChampion

    Points: 10952

    Is that a row or pullback limit of 4000 characters?

    412-977-3526 call/text

  • cyp901

    Mr or Mrs. 500

    Points: 539

    Interesting article...

    In general, my preference is not to have the database server connect to the API directly.

    There are the obvious security reasons but there are also performance considerations.

  • Velveeta22

    Ten Centuries

    Points: 1289

    Bosko Vukov (6/2/2016)


    Hi, the problem is not in the nvarchar type but in the limitation of sp_OAGetProperty in the line:

    EXEC @hr = sp_OAGetProperty @Obj, 'ResponseText', @json OUTPUT

    I believe this is close to correct, looks to be a limitation in sp_OACreate. I will edit the article if possible. Also, JSON_VALUE is limited to 4000 characters, as discussed here at the 15:10 marker of the MSDN video.

    I must have made the assumption that JSON did not support NVARCHAR(MAX) overall. NVARCHAR without the MAX specification is limited to 4000 characters, which just so happens to be the same limitation of sp_OACreate.

    Thanks Bosko

  • Velveeta22

    Ten Centuries

    Points: 1289

    fregatepllada (6/2/2016)


    It's amazing that usage of CLR is no-no in SQL Server , but old-fashion OLE/COM integration is acceptable 😉

    cyp901 (6/2/2016)


    Interesting article...

    In general, my preference is not to have the database server connect to the API directly.

    There are the obvious security reasons but there are also performance considerations.

    My understanding is that CLR forces you to set the database to trustworthy, whereas old-fashion OLE/COM does not. I am trying to read up on the debate of enabling sp_OA and CLR, and they all read pretty much the same "security reasons" without an elaboration. What additional actions should be taken to shore up security if you enable Ole Automation?

  • cyp901

    Mr or Mrs. 500

    Points: 539

    Velveeta22 (6/2/2016)


    fregatepllada (6/2/2016)


    It's amazing that usage of CLR is no-no in SQL Server , but old-fashion OLE/COM integration is acceptable 😉

    cyp901 (6/2/2016)


    Interesting article...

    In general, my preference is not to have the database server connect to the API directly.

    There are the obvious security reasons but there are also performance considerations.

    My understanding is that CLR forces you to set the database to trustworthy, whereas old-fashion OLE/COM does not. I am trying to read up on the debate of enabling sp_OA and CLR, and they all read pretty much the same "security reasons" without an elaboration. What additional actions should be taken to shore up security if you enable Ole Automation?

    I may be mistaken but I believe the trustworthy setting is only needed for certain types of assemblies. Ones that are not in the 'safe' list and ones that require external access of some sort. So to be fair, in this case I think it would be required considering what you are attempting to do.

    One important distinction though is that trustworthy is a database setting whereas enabling sp_oa is a server level setting.

    In a production environment this opens up a large attack vector.

    I believe the process that is created is running as the account used to run SQL server. It inherently goes against the least privilege methodology.

    From a performance standpoint, I don't like the idea using any of the scheduler threads to perform tasks outside of the database, especially tasks that can have an unknown amount of time associated with them. For example: downloading a large file.

    OA also is known to have problems with memory management and so that's something else to deal with from a performance perspective.

    It's not my intention to disparage the entire article though. It is great to see various ways to use the new JSON features.

  • Velveeta22

    Ten Centuries

    Points: 1289

    cyp901 (6/2/2016)


    Velveeta22 (6/2/2016)


    fregatepllada (6/2/2016)


    It's amazing that usage of CLR is no-no in SQL Server , but old-fashion OLE/COM integration is acceptable 😉

    cyp901 (6/2/2016)


    Interesting article...

    In general, my preference is not to have the database server connect to the API directly.

    There are the obvious security reasons but there are also performance considerations.

    My understanding is that CLR forces you to set the database to trustworthy, whereas old-fashion OLE/COM does not. I am trying to read up on the debate of enabling sp_OA and CLR, and they all read pretty much the same "security reasons" without an elaboration. What additional actions should be taken to shore up security if you enable Ole Automation?

    I may be mistaken but I believe the trustworthy setting is only needed for certain types of assemblies. Ones that are not in the 'safe' list and ones that require external access of some sort. So to be fair, in this case I think it would be required considering what you are attempting to do.

    One important distinction though is that trustworthy is a database setting whereas enabling sp_oa is a server level setting.

    In a production environment this opens up a large attack vector.

    I believe the process that is created is running as the account used to run SQL server. It inherently goes against the least privilege methodology.

    From a performance standpoint, I don't like the idea using any of the scheduler threads to perform tasks outside of the database, especially tasks that can have an unknown amount of time associated with them. For example: downloading a large file.

    OA also is known to have problems with memory management and so that's something else to deal with from a performance perspective.

    It's not my intention to disparage the entire article though. It is great to see various ways to use the new JSON features.

    Good feedback. With the 4000 character limit, I'm not sure there is any worry about large data pulls and execution time. I placed my API calls into a loop with STATISTICS TIME ON and did 500 with an average response of 180ms per API call, but results will vary on the different API sources and environments used.

    I still don't fully comprehend the "attack vector" from the Ole Automation, especially if you do not leave the option on full time. Ole automation can be enabled and secured for a user according to this link: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/78f50adc-4d31-4ba0-b6ca-a316d34d4d60/enabling-ole-automation-procedures-for-an-applications-user-accounts-is-this-a-risk?forum=sqlsecurity

    The other option is to just enable Ole Automation in step 1 of an agent job, step 2 execute API call and then disable Ole Automation in step 3. If the call takes milliseconds to grab the data from the API and then Ole Automation is immediately turned off, then it seems like the surface area for the attack vector is extremely small.

  • Bosko Vukov

    Old Hand

    Points: 339

    You need to sign assembly with certificate, and then you don't need to make your database trustworthy. Most of the time you will see articles (on internet) that use trustworthy as shorter solution (to demonstrate something).

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply