Skip to main content

Fix: “Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" and "cannot open database requested by the login the login failed login failed for user"

Access a SQL Server Database from ASP.NET Web Application through Windows Authentication | Integrated Security=SSPI in Web.Config.

 
Documentation tool for SQL Server - Dataedo
 

In this post, let's learn how to access the SQL Server Database from ASP.NET Web Application through Windows Authentication and what are the changes required in SQL server connection string in details.

In general, we used to define the Connection String in Web.Config file to connect a database from a Web Application. That means SQL Server login credentials (User ID and Password) are maintained in Web.Config file as plain text which is not a secured approach. To overcome the security issue, we can implement standard Encrypt and Decrypt mechanism (System.Security.Cryptography) so that credentials can not be stolen or tampered very easily. However, the requirement taken into consideration here is completely different that is

“we need to enable the Windows authentication instead of SQL authentication so that credentials need not be stored in config file.”

Hence, enabling Windows Authentication could be an appropriate solution to this requirement. How to enable the Windows Authentication to connect SQL Server Database?

Windows Authentication for SQL Server database can be enabled with following changes,

1.      ConnectionString need to be modified as follows, you could see that there is no User ID and Password specified in that, the keyword “integrated security=SSPI “takes care the authentication instead.


=========================================================
Advertisement: Choosing .NET Core Worker Services OR Windows Services?

=========================================================

<add key="ConnectionString" value="data source=<SERVER NAME>; initial catalog=<DB NAME>; integrated security=SSPI; persist security info=False;"/>

2.      Authentication mode should be mentioned as Windows only in the Web.Config file.
<authentication mode="Windows" />

3.      Impersonation should be turned on under <system.web> section in Web.Config file
<identity impersonate="true"/>

Otherwise you may get the below error if not turned the Impersonation on.

If SQL Server exist in different machine,

“Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection”


If SQL Server exist in same machine,

“Cannot open database "DBNAME" requested by the login. The login failed. Login failed for user 'MachineName\ASPNET'.”


4.      Finally, In case the SQL Server exists in different machine of the same domain then new login account to be added in SQL Server.
    Go to SQL Server Management Studio, Login with Admin account, Under Security, Logins -> New Login then add new user with domain account details
e.g. DOMAINNAME\USERNAME

After above changes, your web application can access the database without User Credentials, but based on Logged In user's privilege given already.

This seems a very simple right! Of course it is easy to enable the Windows authentication to connect the local SQL server or the SQL Server exists in different machine with in same domain. However, the real challenge will arise only when we enable the Windows Integrated Authentication for remote databases that exist outside the network domains where SQL server exist. How to access a remote SQL Server Database (not exist in same network domain)

Happy Learning!

Labels:

Comments

Post a Comment

Popular posts from this blog

Fix: "Cannot set a credential for principal 'sa' . (Microsoft SQL Server, Error: 15535)" and “User is not associated with a trusted sql server connection" Errors

Recently, I had happen to struck with the following errors when I tried to reset the SA password through the SQL Server 2008 R2 Express. " Cannot set a credential for principal 'sa' . (Microsoft SQL Server, Error: 15535) " and then, “ User is not associated with a trusted sql server connection " From my research I have found the solution and that perfectly worked in SQL management studio. Hence, I thought of sharing my findings with others. ========================================================= Advertisement: Choosing .NET Core Worker Services OR Windows Services? ========================================================= Steps to reset the password in SQL Server 2008 R2 Express and fix for the errors: Step 1. Go to SQL Server Instance -> Properties/Security tab and change the mode to SQL Server authentication mode. Step 2. Go to Security/Logins, then open 'sa' login properties,          a. Uncheck the "Enforce passwor...
59 comments
Read more

How to Implement Lombok in IntelliJ for Java Projects: A Step-by-Step Guide

Lombok in IntelliJ for Java Projects Implementing Lombok in your Java project can streamline your code by reducing boilerplate. Follow these steps to set up Lombok in IntelliJ: Step 1 :  Ensure Java SDK Version. Ensure that your project is using Java 8 or higher.  You can check and set the Java SDK version in pom.xml: XML <properties> <maven.compiler.source>1.8</maven.compiler.source> <maven.compiler.target>1.8</maven.compiler.target> </properties> Step 2 : Add Lombok Dependency to pom.xmlOpen the pom.xml file in your project. Add the following Lombok dependency inside the <dependencies> section: XML <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.24</version> <scope>provided</scope> </dependency> Step 3 : Download Lombok Plugin for IntelliJ IDEAGo to File > Settings (or press Ctrl+Alt+S). Navigate to Plugins. Search for Lombo...
Post a Comment
Read more

How to create a GenAI Talking Avatar ChatBot using Streamlit and Anthropic Claude LLM Model

GenAI Talking Avatar ChatBot   using Streamlit and Anthropic Claude LLM Model GenAI-Talking-Avatar-Chatbot is a web application that allows users to interact with an AI-powered talking chatbot with a static avatar. The chatbot uses AWS Bedrock for generating responses and Google Text-to-Speech (gTTS) for voice output. The backend is built with FastAPI, and the frontend uses Streamlit for the user interface. Features API Backend (api.py) Provides an API endpoint to handle chat requests. Uses AWS Bedrock to generate AI responses in a specified JSON format. Ensures the responses include the message, avatar expression, and voice tone. Includes a health check endpoint to verify the API status. Chat UI (chat_frontend.py) Chat Interface: Provides a chat interface where users can input their queries and receive responses from the AI assistant. Avatar Display: Displays an avatar that changes expressions based on the AI assistant's responses and actions (e.g., thinking, speaking). AI Respons...
Post a Comment
Read more