Unable to connect to SQL Server using a connection string with Windows authentication

0

I'm trying to connect to my single SQL Server Express instance using Windows authentication after following this particular tutorial (Tutorial)

Here is the connection string:

<connectionStrings>
    <add name="EmployeeContext" 
         connectionString="Server=.\SQLEXPRESSDAZ; database=Employee; Integrated Security = SSPI"
         providerName="System.Data.SqlClient"/>
</connectionStrings>

I've been in SSMS and made sure my user mappings are set to db_owner and that the Employee table is the default table for the user: domain\name.

However, after many combinations of key/value pairs I tried after seeing many examples, I get the error:

The system cannot find the file specified

SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server.....

I've even tried creating new users with user mappings to the Employee DB and changing Integrated Security = SSPI to a user id and password, and still I cannot connect to this instance locally.

Here is a screen grab of SSMS:

enter image description here

UPDATE

I've changed the server attribute to: Server=localhost\SQLEXPRESS and added: Initial Catalog=dbo and I have this error now:

Cannot open database "Employee" requested by the login. The login failed. Login failed for user 'xxxxx\DESKTOP-AIE9SAE$'.

In terms of my code (which can been seen in the Tutorial link), it seems to always be referencing this line 15 if it helps:

Employee employee = employeeContext.Employees.Single(emp => emp.EmployeeId == id);

UPDATE 2

SSMS Security Logins and Users

Security Users

c#
sql-server
asked on Stack Overflow Jul 18, 2018 by user1574598 • edited Jul 19, 2018 by user1574598

1 Answer

0

Following your update, have you created logins mapped to your users/roles? I always tend to overlook that.

  • Go to Security > Logins > (right click) New login
  • Select "Windows Authentication" then find your username in the directory
  • Go to the User mapping page to map it with the wanted user/role/database

https://docs.microsoft.com/en-us/sql/relational-databases/lesson-1-connecting-to-the-database-engine?view=sql-server-2017#create-a-windows-authentication-login

To create a user to which this login will refer:

  • Go to Security > Users > (right click) New user
  • Select "Windows user", then fill the username, login (previously created), and default schema
  • Go to the role page and make sure the user has the right one (db_owner?)

Also you can try the connection with an UDL file: just create a blank document and rename the extention to .udl, then execute it. It's a very simple way to diagnose an SQL connection outside of applications.

EDIT: T-SQL script to add login then user:

Creating the login:

CREATE LOGIN [domain\DarrylGriffith] FROM WINDOWS
GO

Creating the user:

Use [Employee];
GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'[darrylgriffith]')
BEGIN
    CREATE USER [darrylgriffith] FOR LOGIN [domain\DarrylGriffith] WITH DEFAULT_SCHEMA=[dbo]
    EXEC sp_addrolemember N'db_owner', N'[darrylgriffith]'
END;
GO

Change the role attributed at the end with the definitive role you want for your user.

answered on Stack Overflow Jul 19, 2018 by ChuckMaurice • edited Jul 19, 2018 by ChuckMaurice

User contributions licensed under CC BY-SA 3.0