I installed VS2005 on my laptop at the office and of course, got the default instance of SQLEXPRESS. When I take my laptop home and log in (I use my NETWORK\login login everywhere), I can't authenticate to use the database -- it doesn't like my login unless I'm physically at my office.
So, thinking I'm clever, I changed my SQLEXPRESS instance to use 'SQL Server and Windows Authentication mode' and tried to use the 'sa' login in my connection string. Login failed for 'sa' and since I had never set a password (yes, I tried using a blank) I executed the following query on my db:
exec sp_password @new='WHATEVERPASSWORD', @loginame = 'sa'
The query works ( ) but it my connection string doesn't and I'm still unable to use the management tool when not on the original network.
So, I thought I'd add a new, super secret special user, but alas, that has completely escaped me. That frightens me a bit because I'd really like to be able to deploy my database to my web host and trust the my connection strings will work.
So, here are my questions:
1. Can I set up my instance so that I don't have to be on the network on which I installed it in order for it to recognize me (since it's self contained on my machine) If so, how
2. How do I create new SQL users
3. Has the connection string changed significantly, and consequently, am I missing something fundamental in my attempts to connect If someone could toss out a working SQL Server Express 2005 connection string that doesn't use Windows authentication, that would be really appreciated.
Here's some info from my box, if it helps.
Microsoft SQL Server Management Studio Express 9.00.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600
Thanks for any help out there.
SQLEXPRESS database ownership, sa, and authentication troubles
wvengineer
ProwlingPanther
You can look into the error log to see additional information on why a login attempt has failed. I think your attempt to login as sa has failed because the login is disabled. You can enable the sa login by executing:
ALTER LOGIN sa ENABLE
1. Yes, you can use SQL logins for authentication.
2. Use CREATE LOGIN and CREATE USER. See Books Online for additional information on both of these.
3. I don't know any significant changes. To troubleshoot why the connection fails, check the error log. The messages there should provide additional information: check the error state. Post the error message from the error log if you need additional help.
Thanks
Laurentiu