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!