Consuming JSON Formatted API Data in 2016

Question

Post reply

Consuming JSON Formatted API Data in 2016

Velveeta22

Ten Centuries

Points: 1289
More actions
June 1, 2016 at 11:39 pm

#400228

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

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

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

fregatepllada SSCommitted Points: 1702 More actions · Answer 1

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 More actions · Answer 2

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: 1702 More actions · Answer 3

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 More actions · Answer 4

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: 1702 More actions · Answer 5

fregatepllada

SSCommitted

Points: 1702

June 2, 2016 at 5:40 am

#1882784

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

Velveeta22 Ten Centuries Points: 1289 More actions · Answer 6

Checking on sp_OA limits, probably the character limit issue.

Velveeta22 Ten Centuries Points: 1289 More actions · Answer 7

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: 11052 More actions · Answer 8

Is that a row or pullback limit of 4000 characters?

412-977-3526 call/text

cyp901 Mr or Mrs. 500 Points: 539 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 13

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 More actions · Answer 14

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).