We help IT Professionals succeed at work.

SQLEXPRESS trusted connection issue

cwest1
cwest1 asked
on
891 Views
Last Modified: 2012-06-21
Well, here I am again with an SQL connection problem :) This time I can't connect SSMS to SQLEXPRESS using SQL Auth (instead of Windows auth, which works). I've set SQLEXPRESS to allow remote connections through TCP and N/Pipes, tried SA and even added a new sysadmin account mapped to a specific db. I always get "the user is not associated a trusted SQL Server" connection error. Appreciate any suggestions. Thanks in advance.
Comment
Watch Question

Commented:
Is the "SQL Server Browser" service at the server running?

Have you restarted the SQL Express service since you changed to mixed authentication?

Has the account you are using the enough privileges on server and database?

Author

Commented:
yes, yes, and I would have thought the SA user had adequate perms. I'm unsure how to check though.

Commented:
oh, using SA, this user has full access as default, sometimes it's disabled, have you checked it?

Author

Commented:
login is enabled for SA

Commented:
check SA user mapping... at SA properties

Author

Commented:
SA is mapped to master (and other dbs that are standard) as dbo

Commented:
is it mapped to the default database you are connecting to?

Author

Commented:
No. Create fails. Cannont use special principal SA. SQLEXPRESS isn't letting me map SA to new dbs.

Commented:
Ok, create another user, give full access to it to test (at server and new db) then try to make a connection with the new user.

Author

Commented:
I tried that before posting. Added user SA2, mapped to mydb as dbo, same error. One thing I just noticed when loking at SA properties, SQL Authentication is grayed out in the status window. That is, it can't be selected. I suspect that might be a registry setting, but I don't know where.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Oh shxx. Mixed mode wasn't selected. Duh. I was assuming that allowing remote connections implied SQL Auth. It never occurred to me that you'd connect remotely with a windows account. Thanks for the guidance. Much appreciated.

Commented:
You're welcome

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.