I just installed SQL Server 2014 Express. I set it up and got it to make a database with tables (I had to log in as administrator to do it).

But now I'm trying to connect to it through C# using ODBC (System.Data.SqlClient), just the normal way by setting up a connection string and opening it. But when I open I get an exception. It says I can't log in with that user, and I tried to set the user to SA (System admin), and I tried to use Windows authentication, and I tried to make my own user in the DBMS and set the user name/password, and have the connection string match them.

No matter what combination of things I try, I still get the error, except if I set the Trusted_Connection=True I get a different error that says it can't even connect to the database. I have the Data Source name set correctly, and the user id and password, and I've tried omitting them as well and attempted to use Windows authentication. I have the Server always set to localhost because it's not on a network. I have everything separated with semicolons and formatted correctly.

I saw an example that said to set ProviderName=System.Data.SqlClient, but when I tried that there was an error that said providername is an invalid parameter. Anyway, I also tried calling it Provider Name, Provider_Name and Provider, but those didn't work either.

I saw something that suggested I make a file called X.something (It's a Microsoft DataLink file so MDL maybe? I don't remember the extension). I opened it and selected a provider (I tried a few different ones). When I tried some general one, and went to the next tab, it give me drop down options of Excel and Access (not applicable for what I'm doing). I went back to the provider tab and tried the SQL Server option, and then on the next tab there was nothing in the dropdown list even if I refreshed it!

Sorry, this is all from memory because it's on a different computer so I can't see it, but I just did it a few minutes ago.

Anyway, does anyone have any ideas? Thanks!

Sorry I didn't mean to post the same thing, I never got a confirmation so I didn't realize it had been posted.

fastcall22, I've created a user, but I'm not sure about a login. I guess I'll try that as soon as I get a chance.

If I recall, you usually have to go in and enable Named Pipes and TCP/IP access, which for some reason is disabled by default with Express, and modify your firewall to allow connections on the SQL ports. It may also be necessary to start the SQL service for the Express database engine, which sometimes I have seen does not get started on install, or is set with a Manual startup type.

How do I enable named pipes and TCP/IP? I don't have a firewall - in fact that computer is airgapped, which makes me wonder, why do I need TCP/IP access at all? I have the connection string configured to use localhost, so shouldn't it just ignore the Internet anyway?

What do you mean "SQL service for the Express database engine"? Is that a specific service, or are you just referring to the DBMS in general?

P.S.:

Does anyone know where the data files get stored, because I can't seem to find them. I changed some of the default paths because I wanted them on a different drive, and there are some directories there, but I can't find where the actual database files are stored (I was looking for them by [name].mdb).

Also, does anyone know how to connect directly to a file and use a database from C# without using the actual DBMS in any way, so that if I were to take a program that needs database connectivity and install it on a computer that doesn't have SQL Server installed, it would still be able to use and manipulate data within a database that has an already established structure?