sql login for ssms

k muthus 41 Reputation points
2022-03-07T06:51:35.54+00:00

need to create an sql login that needs to be used for applicaiton only but it should not used SSMS login

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 137.7K Reputation points MVP Volunteer Moderator
    2022-03-07T22:29:35.953+00:00

    There may be answer for you after all. That depends on the nature of your application. If it is a fat Windows client sitting on the users' desktop, forget it.

    The key is that you need three tiers: The database tier, the application tier and the user's machine. The network is configured so that the user machines cannot reach the SQL Server machine directly. In the system I work with daily, we have the web server and SQL Server on the same machine. We have revoked CONNECT on the TCP Endpoint, so users can only access SQL Server through shared memory. But the only way users can access the machine is through the web server.

    Was this answer helpful?

    0 comments
  2. AmeliaGu-MSFT 13,866 Reputation points Microsoft External Staff
    2022-03-07T10:07:30.803+00:00

    Hi kmuthus-2347,

    Welcome to Microsoft Q&A.

    Do you mean you want to create a SQL Server login to connect to SQL Server? As others mentioned, SSMS is a tool to monitor and administer instances of SQL Server and we can login to SSMS without SSMS login. If I misunderstood, please feel free to let me know.
    To create a SQL Server login, you can right-click the Security folder, point to New, and select Login. Then select SQL Server authentication in the New login page-> General tab.

    Please refer to Create a Login for more details.

    Best Regards,
    Amelia

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Was this answer helpful?

    0 comments
  3. [email protected] 4,321 Reputation points
    2022-03-07T09:47:09.9+00:00

    You can't do that. SQL Server do not distinguish between your "application" and SSMS. Everything that connects to SQL Server is some client application, regardless of if that is your app or SSMS, a BI tool, or whatever.

    You could write a logon trigger that check against the application name from the client and do a rollback. But the application name is provided by the client tool, so this is dead-easy to spoof.

    Instead, make sure that the right people only have access to the password for your application login.

    Was this answer helpful?

    0 comments
  4. Olaf Helper 47,631 Reputation points
    2022-03-07T09:11:18.913+00:00

    SSMS is only a tool and don't have logins as it; the database engine you are going to connect to using SSMS do have login information.

    I don't understand what your issue is or what you try to achieve here; may can you explain it more detailed, please?

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.