I am trying to login to a SQL Server 2014 from another service (AWS Schema Conversion Tool). In the AWS tool I need to input 'User name' and 'Password'. Currently I am using windows authentication for connection to a MS SQL DB. My user name as per SSMS is 'Server\user'. I have tried logging into the DB from the AWS tool using user as 'Server\user' and 'user' with my windows password but keep getting a 'Incorrect username or password alert'.
To login from the AWS tool I would like to create a dummy user who has admin privileges and SQL Server Authentication. In SSMS have tried
Server->Databases->DB->Security->New user.
I created a new dummy windows admin user the tried to create 'SQL user with login' and got the error 'Create failed for user 'dummy''. ''dummy' is not a valid login or you do not have permission'. I am logged in with admin rights and 'dummy' definitely exists as a windows user.
Any ideas on how I can create a new user with SQL Server Authentication?
Best Answer
Hope these links help to fix your problem ---
https://stackoverflow.com/questions/6643720/add-login-and-connect-to-sql-with-sql-server-authentication
https://movabletype.org/documentation/mt5/advanced/creating-a-sql-server-database-2.html
The message you are receiving basically means "I can't connect to SQL Server". It can be due to a number of issues:
The database is using windows authentication and the user is not authorized orThe database is using mixed-mode authentication and the user/pass is incorrect As for the usernames you are given, I would assume that the "administrator" username is local windows administrator of the machine SQL server is running on, and that the "sa" user is the default database administrator, that is installed when setting SQL Server to use mixed-mode authentication. So, I would recommend to try connecting to the database using Mixed-mode authentication, with the "sa" username the "anotherpassword" password.