Tuesday, August 24, 2021

Cannot create Sql Source. Error: The value of the property 'Value' is invalid for the stored procedure parameter 'XXX'


Error/Issue:

Today got below error while Debug run of newly developed pipeline, in which at the end am logging audit details in a table via Stored procedure


The value of property is invalid for stored procedure


                             


Why it happened


Cannot create Sql Source. Please double check the connection string, stored procedure are set with correct format. Error: The value of the property 'Value' is invalid for the stored procedure parameter 'OperationsProcessed'




As stated in error message(highlighted in red above) the error says the value passed against 'OperationsProcessed ' parameter is incorrect

It was surprising as this parameter is defined as bit  in stored procedure so the value can be either 0 or 1 and upon rechecking the value - I see nothing wrong  i.e. 0 (zero)

bit to boolean in stored procedure parameter


When checked the datatype, there was no bit instead it was Boolean, it is implicitly done by ADF  - and this is the reason for the error .

What to do



So to fix the error, we need to provide valid Boolean values i.e. either true or false.

That's it, provided false as value instead of 0(zero) and that made the error go away.

You may ask, what value do you see in Table(stored procedure inserts in audit table)? 

-- It is 0 and not false 

So I assume, ADF implicitly handles this type conversion.

Sunday, August 22, 2021

Cannot connect to SQL Database:'XXX' . Verify that the instance name is correct


Error/Issue:


While creating linked service to connect to Azure SQL Database, after configuring clicked on Test connection and got below error


Cannot connect to SQL Database: 'demosqlserveradf', Database: 'ADFDemo', User: 'adfdemouser'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server), SqlErrorNumber=53,Class=20,State=0, The network path was not found Activity ID: 0ebaa64f-d5b7-48bb-a9f9-21e1fdac28a3.

                             


Why it happened


It was silly mistake, and as stated in error message(highlighted in red above) the SQL Server Instance name was incorrect.

As can be seen in below image, instance name is without domain, thus the resolution of it doesn't happen and as no specific address was resolved(ip address) thus linked service couldn't connect .


Incorrect SQL Server Instance Name

What to do



Just need to provide full name of SQL Server i.e. SQL Server Instance name with domain (azure provided or custom domain - if you have one)

            demosqlserveradf.database.windows.net


Doing this fixes the issue, connection was established  - as now when linked service looks up for above it gets the resolved address.


Saturday, August 21, 2021

Cannot open server 'xxx' requested by the login. Client with IP address 'x.x.x.x' is not allowed to access the server

Error/Issue:


While I was trying to create a Linked Service for Azure based SQL database, upon clicking on Test Connection, got below error

Client with IP address is not allowed to access the server


                              

   

Cannot connect to SQL Database: 'demosqlserveradf.database.windows.net', Database: 'ADFDemo', User: 'adfdemouser'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. Cannot open server 'demosqlserveradf' requested by the login. Client with IP address '52.140.104.134' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect., SqlErrorNumber=40615,Class=14,State=1, Activity ID: e6201b40-a98f-4d76-98b0-8fc724c0eb00.






Why it happened


As the error says the connection is not established and three  probable reasons as to why it happened, let's see which is the actual reason

1.SQL Database firewall allows the integration runtime to access.  -- The firewall settings has Deny public network access set to No, thus access over internet is possible
2.Cannot open server 'demosqlserveradf' requested by the login. -- Credentials to open Server is correct (using SSMS it works) 
3.Client with IP address '52.140.104.134' is not allowed to access the server -- Here it says client is not allowed and this is the actual reason and it is because (highlighted in below image)

                       Allow Azure services and resources to access this server is set to No 


Allow Azure services and resources to access this server





What to do


Just moving the radio button from No to Yes against setting - 

Allow Azure services and resources to access this server .



Learn More about some more Azure Data Factory errors