[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQLEXPRESS trusted connection issue

Posted on 2007-09-28
13
Medium Priority
?
864 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.
0
Comment
Question by:cwest1
  • 7
  • 6
13 Comments
 
LVL 5

Expert Comment

by:fmonroy
ID: 19980330
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?
0
 

Author Comment

by:cwest1
ID: 19980345
yes, yes, and I would have thought the SA user had adequate perms. I'm unsure how to check though.
0
 
LVL 5

Expert Comment

by:fmonroy
ID: 19980407
oh, using SA, this user has full access as default, sometimes it's disabled, have you checked it?
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:cwest1
ID: 19980534
login is enabled for SA
0
 
LVL 5

Expert Comment

by:fmonroy
ID: 19980563
check SA user mapping... at SA properties
0
 

Author Comment

by:cwest1
ID: 19980601
SA is mapped to master (and other dbs that are standard) as dbo
0
 
LVL 5

Expert Comment

by:fmonroy
ID: 19980632
is it mapped to the default database you are connecting to?
0
 

Author Comment

by:cwest1
ID: 19980701
No. Create fails. Cannont use special principal SA. SQLEXPRESS isn't letting me map SA to new dbs.
0
 
LVL 5

Expert Comment

by:fmonroy
ID: 19980841
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.
0
 

Author Comment

by:cwest1
ID: 19981151
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.
0
 
LVL 5

Accepted Solution

by:
fmonroy earned 2000 total points
ID: 19981179
right click the server, properties, security... what's selected there?
0
 

Author Comment

by:cwest1
ID: 19981246
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.
0
 
LVL 5

Expert Comment

by:fmonroy
ID: 19981298
You're welcome
0

Featured Post

When ransomware hits your clients, what do you do?

MSPs: Endpoint security isn’t enough to prevent ransomware.
As the impact and severity of crypto ransomware attacks has grown, Webroot has fought back, not just by building a next-gen endpoint solution capable of preventing ransomware attacks but also by being a thought leader.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With the evolution of technology, we have finally reached a point where it is possible to have home automation features like having your thermostat turn up and door lock itself when you leave, as well as a complete home security system. This is a st…
This article is about my experience upgrading my consulting machine to Windows 10 Version 1709 (The Fall 2017 Creator Update)
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question