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

Question

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:

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

Please post how you are using that connection string. The error message reads as if you are trying to use a connection string with `AttachDbFilename` — Camilo Terevinto, Jul 18 '18 at 20:43
right click on your database -> property -> connection string — Steve, Jul 18 '18 at 20:46
Twice in the question you are referring to `Employee` as a table. The screenshot clearly shows it is the database. The table is called Employees. It is very important that you can tell the difference, and that you use the correct words. Also SMSS should probably be SSMS (twice). Please [edit] your question. — Richardissimo, Jul 18 '18 at 21:11
Are you are running the application on the same machine where the database is? And there are lots of very similar questions on SO... which of those answers have you ruled out? — Richardissimo, Jul 18 '18 at 22:05
@Richardissimo I have amended my post to correct such mistakes. — user1574598, Jul 19 '18 at 08:47
Possible duplicate of [Cannot open database "test" requested by the login. The login failed. Login failed for user 'xyz\ASPNET'](https://stackoverflow.com/questions/2575907/cannot-open-database-test-requested-by-the-login-the-login-failed-login-fail) — Richardissimo, Jul 19 '18 at 09:11

ChuckMaurice · Answer 1 · 2018-07-19T14:01:55.500

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://learn.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.

edited Jul 19 '18 at 14:01

answered Jul 19 '18 at 09:37

ChuckMaurice

111
7

Thanks - I have tried adding a New Login as you suggested, but I get an exception telling me I can't create a duplicate New Login. I've tried editing the properties of the login so I am mapped to the Employee DB but I get an exception telling me that I cannot alter the user `dbo` – user1574598 Jul 19 '18 at 11:41
I'm gonna edit my post with T-SQL instructions so you can repeat the steps with a single query without errors – ChuckMaurice Jul 19 '18 at 11:58
What account are you using for SSMS? – ChuckMaurice Jul 19 '18 at 12:19
I've edited my post with more screen grabs from SSMS. – user1574598 Jul 19 '18 at 12:40
So there's the login, but you don't have any user associated. If you were trying to use the dbo user, you can't (but you can use the role). Follow my script without the two first lines that creates the login. – ChuckMaurice Jul 19 '18 at 12:52
So, for example `CREATE USER [domain\bob] FOR LOGIN [domain\DarrylGriffiths] .......... `. Do I use the former or latter for the last`[domain\username]` in the script? So, is this because `domain\DarrylGriffiths` is not within the `users` folder within the `Employee` DB? Also, do I run this script within SSMS? – user1574598 Jul 19 '18 at 13:41
the username you choose for your user is not relevant there, but it's preferable to use something similar to the login. You could just use [darrylgriffith] instead of [domain\bob] for exemple. Yes, you can run the script with the "new query" button, then execute. – ChuckMaurice Jul 19 '18 at 13:45
Ok, could you please edit your script to accommodate the relevant username you specified and the current username as I'm a bit confused where to place them in your placeholders. – user1574598 Jul 19 '18 at 13:56
done. This should work if Windows recognizes the login. – ChuckMaurice Jul 19 '18 at 14:03
Unfortunately the above did not work - can't believe the difficulty in connecting to a local DB. Will update this if I have any luck in the future. Thanks anyway for your help. – user1574598 Jul 20 '18 at 13:34

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

1 Answers1