Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

sql server 2008 permissions for user connecting from another machine on same network not as sysadmin

I have 2 machines and 2 users for MS SQL server 2008 R2
Both machines belong to the same domain network.

machine A is a windows 7 pro pc trying to setup an odbc connection to machine B ( sql server)running
machine B windows 2008 r2 server running sql server

I am setting up an odbc on machine a to access machine b
       If i setup the connection as "sa" the connection works fine.
       If i setup the connection as "myuser" i cant connect to the server getting error

As a suggestion i tried adding "sysadmin" role to "myuser" and i am able to connect ok, but i dont think this is the right way to do it. Looking to see what actually needs to be setup for myuser to be able to connect not as a sysadmin.
0
Extreme66
Asked:
Extreme66
  • 6
  • 4
1 Solution
 
Beth KludtIndependent Business OwnerCommented:
Did you grant "myuser" access to the target database in addition to creating its login?
0
 
Extreme66Author Commented:
Yes, thanks
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What's the message error?
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Extreme66Author Commented:
I should have put that in already, thanks
Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'myuser'.

I am able to login to enterprise manager with the myuser id however and see my databases.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Enterprise Manager?
Shouldn't be Management Studio?
Enterprise Manager is from SQL Server 2000 and can't connect to superior versions of SQL Server.
0
 
Extreme66Author Commented:
Sorry had old terminology in my head, yes its Microsoft SQL Management Studio
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The error appears when you create the DSN in ODBC?
And which permissions has that user in the SQL Server instance?
0
 
Extreme66Author Commented:
Do you mean sql server permissions or windows permissions?
server roles -> public
user mapping -> db_datareader and public
status grant and enabled
no securables
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Apparently you have it well configured.
Can't figure out from here. Sorry.
0
 
Extreme66Author Commented:
thanks for the help but i actually figured it out, after server hardening appearantly i needed to add this final grant.

GRANT CONNECT ON ENDPOINT::"TSQL Default TCP" TO "myuser"
0
 
Extreme66Author Commented:
Hopefully this solution finds others, as it would have saved me TONS of time due to an non descriptive error
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now