Hi I want to use a web job to call the following code:
using (SqlConnection con = new SqlConnection(ConfigStore.RemoteConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(xxxxxx, con))
{
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
}
while (reader.Read())
{
}
reader.Close();
}
}
Im getting the following error
018-07-12T07:13:25 PID[4540] Information Microsoft.Azure.WebJobs.Host.FunctionInvocationException : Exception while executing function: Functions.FiveSecondTask ---> System.Data.SqlClient.SqlException : 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) ---> System.ComponentModel.Win32Exception : Access is denied 2018-07-12T07:13:25 PID[4540] Information End of inner exception 2018-07-12T07:13:25 PID[4540] Information at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject,UInt32 waitForMultipleObjectsTimeout,Boolean allowCreate,Boolean onlyOneCheckConnection,DbConnectionOptions userOptions,DbConnectionInternal& connection) 2018-07-12T07:13:25 PID[4540] Information at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject,TaskCompletionSource
1 retry,DbConnectionOptions userOptions,DbConnectionInternal& connection) 2018-07-12T07:13:25 PID[4540] Information at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection,TaskCompletionSource1 retry,DbConnectionOptions userOptions,DbConnectionInternal oldConnection,DbConnectionInternal& connection) 2018-07-12T07:13:25 PID[4540] Information at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection,DbConnectionFactory connectionFactory,TaskCompletionSource1 retry,DbConnectionOptions userOptions) 2018-07-12T07:13:25 PID[4540] Information at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection,DbConnectionFactory connectionFactory,TaskCompletionSource1 retry,DbConnectionOptions userOptions) 2018-07-12T07:13:25 PID[4540] Information at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) 2018-07-12T07:13:25 PID[4540] Information at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) 2018-07-12T07:13:25 PID[4540] Information at System.Data.SqlClient.SqlConnection.Open() 2018-07-12T07:13:25 PID[4540] Information at WebJob5.Functions.FiveSecondTask(TimerInfo timer) 2018-07-12T07:13:25 PID[4540] Information at lambda_method(Closure ,Functions ,Object[] ) 2018-07-12T07:13:25 PID[4540] Information at Microsoft.Azure.WebJobs.Host.Executors.VoidMethodInvoker2.InvokeAsync(TReflected instance,Object[] arguments) 2018-07-12T07:13:25 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionInvoker2.InvokeAsync[TReflected,TReturnValue](Object instance,Object[] arguments) 2018-07-12T07:13:25 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.InvokeAsync(IFunctionInvoker invoker,ParameterHelper parameterHelper,CancellationTokenSource timeoutTokenSource,CancellationTokenSource functionCancellationTokenSource,Boolean throwOnTimeout,TimeSpan timerInterval,IFunctionInstance instance) 2018-07-12T07:13:25 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithWatchersAsync(IFunctionInstance instance,ParameterHelper parameterHelper,TraceWriter traceWriter,CancellationTokenSource functionCancellationTokenSource) 2018-07-12T07:13:25 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithLoggingAsync(??) 2018-07-12T07:13:25 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithLoggingAsync(??) 2018-07-12T07:13:25 PID[4540] Information End of inner exception 2018-07-12T07:13:26 PID[4540] Information Executed 'Functions.FiveSecondTask' (Failed, Id=beb15926-66b3-4f7e-900a-ebf855feb07b) 2018-07-12T07:13:26 PID[4540] Information Microsoft.Azure.WebJobs.Host.FunctionInvocationException : Exception while executing function: Functions.FiveSecondTask ---> System.Data.SqlClient.SqlException : 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) ---> System.ComponentModel.Win32Exception : Access is denied 2018-07-12T07:13:26 PID[4540] Information End of inner exception 2018-07-12T07:13:26 PID[4540] Information at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject,UInt32 waitForMultipleObjectsTimeout,Boolean allowCreate,Boolean onlyOneCheckConnection,DbConnectionOptions userOptions,DbConnectionInternal& connection) 2018-07-12T07:13:26 PID[4540] Information at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject,TaskCompletionSource1 retry,DbConnectionOptions userOptions,DbConnectionInternal& connection) 2018-07-12T07:13:26 PID[4540] Information at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection,TaskCompletionSource1 retry,DbConnectionOptions userOptions,DbConnectionInternal oldConnection,DbConnectionInternal& connection) 2018-07-12T07:13:26 PID[4540] Information at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection,DbConnectionFactory connectionFactory,TaskCompletionSource1 retry,DbConnectionOptions userOptions) 2018-07-12T07:13:26 PID[4540] Information at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection,DbConnectionFactory connectionFactory,TaskCompletionSource1 retry,DbConnectionOptions userOptions) 2018-07-12T07:13:26 PID[4540] Information at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) 2018-07-12T07:13:26 PID[4540] Information at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) 2018-07-12T07:13:26 PID[4540] Information at System.Data.SqlClient.SqlConnection.Open() 2018-07-12T07:13:26 PID[4540] Information at WebJob5.Functions.FiveSecondTask(TimerInfo timer) 2018-07-12T07:13:26 PID[4540] Information at lambda_method(Closure ,Functions ,Object[] ) 2018-07-12T07:13:26 PID[4540] Information at Microsoft.Azure.WebJobs.Host.Executors.VoidMethodInvoker2.InvokeAsync(TReflected instance,Object[] arguments) 2018-07-12T07:13:26 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionInvoker2.InvokeAsync[TReflected,TReturnValue](Object instance,Object[] arguments) 2018-07-12T07:13:26 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.InvokeAsync(IFunctionInvoker invoker,ParameterHelper parameterHelper,CancellationTokenSource timeoutTokenSource,CancellationTokenSource functionCancellationTokenSource,Boolean throwOnTimeout,TimeSpan timerInterval,IFunctionInstance instance) 2018-07-12T07:13:26 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithWatchersAsync(IFunctionInstance instance,ParameterHelper parameterHelper,TraceWriter traceWriter,CancellationTokenSource functionCancellationTokenSource) 2018-07-12T07:13:26 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithLoggingAsync(??) 2018-07-12T07:13:26 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithLoggingAsync(??) 2018-07-12T07:13:26 PID[4540] Information End of inner exception 2018-07-12T07:13:26 PID[4540] Information at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() 2018-07-12T07:13:26 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithLoggingAsync(??) 2018-07-12T07:13:26 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.TryExecuteAsync(IFunctionInstance functionInstance,CancellationToken cancellationToken) 2018-07-12T07:13:26 PID[4540] Information Function had errors. See Azure WebJobs SDK dashboard for details. Instance ID is 'beb15926-66b3-4f7e-900a-ebf855feb07b' 2018-07-12T07:13:26 PID[4540] Information Microsoft.Azure.WebJobs.Host.FunctionInvocationException : Exception while executing function: Functions.FiveSecondTask ---> System.Data.SqlClient.SqlException : 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) ---> System.ComponentModel.Win32Exception : Access is denied 2018-07-12T07:13:26 PID[4540] Information End of inner exception 2018-07-12T07:13:26 PID[4540] Information at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject,UInt32 waitForMultipleObjectsTimeout,Boolean allowCreate,Boolean onlyOneCheckConnection,DbConnectionOptions userOptions,DbConnectionInternal& connection) 2018-07-12T07:13:26 PID[4540] Information at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject,TaskCompletionSource1 retry,DbConnectionOptions userOptions,DbConnectionInternal& connection) 2018-07-12T07:13:26 PID[4540] Information at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection,TaskCompletionSource1 retry,DbConnectionOptions userOptions,DbConnectionInternal oldConnection,DbConnectionInternal& connection) 2018-07-12T07:13:26 PID[4540] Information at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection,DbConnectionFactory connectionFactory,TaskCompletionSource1 retry,DbConnectionOptions userOptions) 2018-07-12T07:13:26 PID[4540] Information at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection,DbConnectionFactory connectionFactory,TaskCompletionSource1 retry,DbConnectionOptions userOptions) 2018-07-12T07:13:26 PID[4540] Information at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) 2018-07-12T07:13:26 PID[4540] Information at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) 2018-07-12T07:13:26 PID[4540] Information at System.Data.SqlClient.SqlConnection.Open() 2018-07-12T07:13:26 PID[4540] Information at WebJob5.Functions.FiveSecondTask(TimerInfo timer) 2018-07-12T07:13:26 PID[4540] Information at lambda_method(Closure ,Functions ,Object[] ) 2018-07-12T07:13:26 PID[4540] Information at Microsoft.Azure.WebJobs.Host.Executors.VoidMethodInvoker2.InvokeAsync(TReflected instance,Object[] arguments) 2018-07-12T07:13:26 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionInvoker2.InvokeAsync[TReflected,TReturnValue](Object instance,Object[] arguments) 2018-07-12T07:13:26 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.InvokeAsync(IFunctionInvoker invoker,ParameterHelper parameterHelper,CancellationTokenSource timeoutTokenSource,CancellationTokenSource functionCancellationTokenSource,Boolean throwOnTimeout,TimeSpan timerInterval,IFunctionInstance instance) 2018-07-12T07:13:26 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithWatchersAsync(IFunctionInstance instance,ParameterHelper parameterHelper,TraceWriter traceWriter,CancellationTokenSource functionCancellationTokenSource) 2018-07-12T07:13:26 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithLoggingAsync(??) 2018-07-12T07:13:26 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithLoggingAsync(??) 2018-07-12T07:13:26 PID[4540] Information End of inner exception 2018-07-12T07:13:26 PID[4540] Information at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() 2018-07-12T07:13:26 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithLoggingAsync(??) 2018-07-12T07:13:26 PID[4540] Information at async Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.TryExecuteAsync(IFunctionInstance functionInstance,CancellationToken cancellationToken)
How would I make sure that the azure can connect to this remote sql box?
How would I achieve this without compromising security?
Many thanks
