Thursday, 25 February 2021

How to give SQL User access to execute Stored procedures and read/write access to database

Assumptions: stored procedures and tables are created under schema dbo. in case different schema, change the below code accordingly. 


 --Create Login with a strong password

--Run the below code in Master Database

CREATE LOGIN [sanjeebuser] WITH PASSWORD = 'VeryStrongPa55w0rD1!'

--run the below code in your Selected Database, (optional: run in master database as well)

CREATE USER [sanjeebuser]

 FOR LOGIN [sanjeebuser]

 WITH DEFAULT_SCHEMA =  dbo

GO


--Run the below code in your selected Database. please do not run the code in Master database.

CREATE ROLE [dbstoregeprocedureaccess] AUTHORIZATION [dbo]

GRANT EXECUTE,ALTER,DELETE,UPDATE,INSERT ON SCHEMA::dbo TO dbstoregeprocedureaccess;

GRANT CREATE PROCEDURE TO dbstoregeprocedureaccess;



EXEC sp_addrolemember N'db_datareader', N'sanjeebuser'

EXEC sp_addrolemember N'db_datawriter', N'sanjeebuser'

EXEC sp_addrolemember N'dbstoregeprocedureaccess', N'sanjeebuser'


Thursday, 11 February 2021

How to Enable Cors in .net standard API ( or .net core API) for PUT GET and DELETE Methods

 Error:

Access to fetch at 'https://localhost:44340/api/employee' from origin 'https://localhost:44339' has been blocked by CORS policy: Method PUT is not allowed by Access-Control-Allow-Methods in preflight response.


:44340/api/employee:1 Failed to load resource: net::ERR_FAILED


blazor.webassembly.js:1 crit: Microsoft.AspNetCore.Components.WebAssembly.Rendering.WebAssemblyRenderer[100]


      Unhandled exception rendering component: TypeError: Failed to fetch


WebAssembly.JSException: TypeError: Failed to fetch


  at System.Net.Http.WebAssemblyHttpHandler.doFetch (System.Threading.Tasks.TaskCompletionSource`1[TResult] tcs, System.Net.Http.HttpRequestMessage request, System.Threading.CancellationToken cancellationToken) <0x2fcb610 + 0x00a30> in <filename unknown>:0 


  at System.Net.Http.WebAssemblyHttpHandler.SendAsync (System.Net.Http.HttpRequestMessage request, System.Threading.CancellationToken cancellationToken) <0x2f9dd40 + 0x00174> in <filename unknown>:0 


  at Microsoft.Extensions.Http.Logging.LoggingHttpMessageHandler.SendAsync (System.Net.Http.HttpRequestMessage request, System.Threading.CancellationToken cancellationToken) <0x2f98030 + 0x00160> in <filename unknown>:0 


  at Microsoft.Extensions.Http.Logging.LoggingScopeHttpMessageHandler.SendAsync (System.Net.Http.HttpRequestMessage request, System.Threading.CancellationToken cancellationToken) <0x2f71090 + 0x00182> in <filename unknown>:0 


  at System.Net.Http.HttpClient.FinishSendAsyncBuffered (System.Threading.Tasks.Task`1[TResult] sendTask, System.Net.Http.HttpRequestMessage request, System.Threading.CancellationTokenSource cts, System.Boolean disposeCts) <0x3394518 + 0x00278> in <filename unknown>:0 


  at sanjeebblazorapp.App.Services.EmployeeDataService.UpdateEmployee (sanjeebblazorapp.Shared.Employee employee) [0x00051] in C:\Users\sanje\source\repos\sanjeebko\sanjeebtutorial-blazor\sanjeebblazorapp.App\sanjeebblazorapp.App\Services\EmployeeDataService.cs:42 


  at sanjeebblazorapp.App.Pages.EmployeeEdit.HandleValidSubmit () [0x001b0] in C:\Users\sanje\source\repos\sanjeebko\sanjeebtutorial-blazor\sanjeebblazorapp.App\sanjeebblazorapp.App\Pages\EmployeeEdit.cs:81 


  at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion (System.Threading.Tasks.Task task) <0x3395450 + 0x000da> in <filename unknown>:0 


  at Microsoft.AspNetCore.Components.Forms.EditForm.HandleSubmitAsync () <0x3307b00 + 0x00218> in <filename unknown>:0 


  at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion (System.Threading.Tasks.Task task) <0x3395450 + 0x000da> in <filename unknown>:0 


  at Microsoft.AspNetCore.Components.RenderTree.Renderer.GetErrorHandledTask (System.Threading.Tasks.Task taskToHandle) <0x2fb1590 + 0x000b6> in <filename unknown>:0 

Solution:

TO enable cors in .net core web API, cors policy need to be added in the services. Below code in blue color needs to be added in the Startup.cs file

public void ConfigureServices(IServiceCollection services)

        {

            

            services.AddDbContext<AppDbContext>(options =>

                options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

   

            services.AddCors(options =>

            {

                options.AddPolicy("Open", builder => builder.AllowAnyOrigin().AllowAnyHeader().WithMethods("PUT","DELETE","GET"));

            });


            services.AddControllers(); 

        }


 public void Configure(IApplicationBuilder app, IWebHostEnvironment env)

        {

            if (env.IsDevelopment())

            {

                app.UseDeveloperExceptionPage();

            }


            app.UseHttpsRedirection();


            app.UseRouting();


            app.UseAuthorization();


            app.UseCors("Open");


            app.UseEndpoints(endpoints =>

            {

                endpoints.MapControllers();

            });

        }

Tuesday, 9 February 2021

Create User Group in Azure using Azure CLI (powershell)

Steps1 - Login to the azure account with  right account with azure AD access. 

Step 2 - Open CloudShell by clicking the icon on the top right corner.

Step 3 - You'll need to associate CLI session with a Storage account. So follow the instruction and connect with the storage account. 

Step 4 - Select the PowerShell in the dropdown window 


You'll see something like this screenshot:



Step 5 - type the below command and press enter:

 PS /home/sanjeeb> Connect-AzureAd

 Step 6 - type the below code (change the group name as your need):

PS /home/sanjeeb>New-AzureADGroup -Description "siteadmingroup" -DisplayName "siteadmingroup" -MailEnabled $false -SecurityEnabled $true -MailNickName "siteadmingroup"

On success, you'll see the object id like below: 

ObjectId                             DisplayName             Description

--------                             -----------             -----------

daf23147-8123-dc23-bsb8-d2d2a5224271 siteadmingroup siteadmingroup

simple use of Record in c#9 and newer versions

 c# 9 introduces a new keyword Record which makes an object immutable. Properties in the record can be initialised at the time of creation o...