log4net , handling Windows Azure SQL Database Connection String

1
votes
  Having a web.config connection for log4net and works fine ( without tcp/ port etc ) 
  Now Using connection details from Windows Azure SQL Database (formerly SQL Azure) Connection String

web.config Eg: Data Source=tcp:AXXXXXX03,1433;Initial Catalog=CXXUserDB;User ID=CXXUserID;Password=CXXUserPassword;Persist Security Info=False;

  When I running the application  getting the below error ( captured  using Diagnostics property ) 
  ERROR [AdoNetAppender] ErrorCode: GenericFailure. Exception while writing to database
  System.Data.SqlClient.SqlException (0x80131904): The INSERT permission was denied on the object 'MyTable'
  MyTable having identity and index also defined 

 Question , What are the changes we have to do when we use Azure Connection String instead of our old connection in web.config. ?
2
loggingazure-sql-databaselog4net

2 Answers

1
votes

Please provide permissions to INSERT on that database to the database user. Use SQL Server Management Studio to run the following statements on the user database.

-- Run this on the user database not on master database 
EXEC sp_addrolemember 'db_datawriter',  'CXXUserID'

You can also assign it read permission on the database.

EXEC sp_addrolemember 'db_datareader',  'CXXUserID'
1
votes
Created a table with clustered index and given permission 

EXEC sp_addrolemember 'db_datawriter',  'CXXUserID'
EXEC sp_addrolemember 'db_datareader',  'CXXUserID'



CREATE TABLE [dbo].[Log4Net] 
(
[Id] [int] IDENTITY (1, 1) NOT NULL,
[Date] [datetime] NOT NULL,
[Thread] [varchar] (255) NOT NULL,
[Level] [varchar] (50) NOT NULL,
[Logger] [varchar] (255) NOT NULL,
[Message] [varchar] (4000) NOT NULL,
[Exception] [varchar] (2000) NULL,
CONSTRAINT [PK_MyLog] PRIMARY KEY CLUSTERED ([Id] ASC)
)
 few more information available : https://blog.tallan.com/2017/07/18/log-management-with-log4net-and-microsoft-azure/