Asked
Viewed
26k times
I am new to SQL Server. Please help me to fix.
I have created a user with password in SQL Server Management Studio (2008 R2). When I use sqlcmd with this user, sqlcmd says
C:\Console2>sqlcmd -U admin
Password:
Msg 18456, Level 14, State 1, Server machinename, Line 1
Login failed for user ‘admin’.
I can access the server with this user using SQL Server Management Studio.
P.S
- I have two instance of SQL Server Management Studio (2008 R2 and 2012). I can access that user from both version of SQL Server Management Studio.
- Whenever I connect using
sqlcmdand executeselect @@versionquery, it always says 2012, even I am connecting 2008 R2’ssqlcmd.
marc_s
734k176 gold badges1332 silver badges1460 bronze badges
asked May 17, 2013 at 12:27
0
It is possible that you have multiple server instances on your computer. Some MS products install the SQL Server Developer edition unless you explicitly tell it not to. So, unless you are explicitly telling sqlcmd which instance to connect to, it may be connecting to something you aren’t expecting.
sqlcmd -S servername -d database name -U id
marc_s
734k176 gold badges1332 silver badges1460 bronze badges
answered May 17, 2013 at 13:04
dazedandconfuseddazedandconfused
3,1661 gold badge18 silver badges29 bronze badges
3
Try this:
-S MyServer\MyInstance
and for the database as well
marc_s
734k176 gold badges1332 silver badges1460 bronze badges
answered May 17, 2013 at 13:11
granadaCodergranadaCoder
26.4k10 gold badges113 silver badges148 bronze badges
By default login failed error message is nothing but a client user connection has been refused by the server due to mismatch of login credentials. First task you might check is to see whether that user has relevant privileges on that SQL Server instance and relevant database too, thats good. Obviously if the necessary prvileges are not been set then you need to fix that issue by granting relevant privileges for that user login.
Check out this post, it shows how to trouble-shoot this error.
answered May 17, 2013 at 13:12
RahulRahul
5,6036 gold badges35 silver badges57 bronze badges
3
- Remove From My Forums
-
Question
-
I created a new user called test2 using the management console. However when I attempt to use sqlcmd to login to it (from the local machine) I cant Login failed.
Any suggestions?
Answers
-
Hi gherty,
Try use
C:\Users\Administrator>sqlcmd -U test2 -P 1234 -S ComputerName\InstanceNameIf you connect to a default instance, you just use ComputerName replace ComputerName\InstanceName. I try it and could connect to the instance successfully.
Make sure that you have chosen SQL Server and Windows Authentication mode in the Server Properties.
If the user name and password combination is incorrect, the OLE DB provider generates an error message.
If you are connecting to the SQL Server server by using SQL Server Authentication, you must also provide the logon information for connecting to the SQL Server server. Make sure that test2 is the login of the instance not the user name of a database.
Sqlcmd Utility:
http://msdn.microsoft.com/en-us/library/ms162773(v=sql.105).aspx.TechNet
Subscriber Support
If you are TechNet
Subscriptionuser and have any feedback on our support quality, please send your feedback
here.Thanks,
Maggie
Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
-
Proposed as answer by
Sunday, September 23, 2012 10:33 PM
-
Marked as answer by
Maggie Luo
Sunday, September 30, 2012 3:47 AM
-
Proposed as answer by
I am trying to run a sql query from powershell for Windows SQL Server 2008 R2. It looks like the code is failing to pass SQL Server Authentication credentials to run the sqlcmd correctly. I am running this first part directly from powershell, not as a script.
Set-Location SQLServer:\SQL\Server\
Invoke-sqlcmd "SELECT DB_NAME() AS hist;" -username "username" -password "p@ss"
I receive error, Login failed for user 'username'. The ideal end result for the code format would look more like this. Same resulting error.
$path = "sqldbdump.csv"
$server = "serveronlocalhost"
$db = "hist"
$Query = @"
SELECT * From alarms;
"@
Invoke-Sqlcmd -ServerInstance $server -Database $db -Username $username -Password $pw -Query $Query | Export-CSV $path
I have also tried convert-tosecurestring -asplaintext with no success.
$pw = convertto-securestring -AsPlainText -Force -String "p@ss"
asked Mar 11, 2014 at 20:13
snoopsnoop
3793 gold badges8 silver badges21 bronze badges
5
Never use double quotes for a variable containing a password because special characters are interpreted (the same for username containing special characters). It is not the case with single quoted strings.
For example, this password will cause an authentification error because the character $ is evaluated:
$pw = "p@ss$ord!"
But if you use single quotes, it will work:
$pw = 'p@ss$ord!'
Please note that the same principle applies to command lines, so this wil not work:
Invoke-Sqlcmd ... -Password "p@ss$ord!"
You should write:
Invoke-Sqlcmd ... -Password 'p@ss$ord!'
answered Aug 25, 2016 at 11:37
Benjamin BALETBenjamin BALET
9191 gold badge11 silver badges31 bronze badges
For everyone that might experience this issue in the future. Run your Invoke-SqlCmd directly through the PowerShell terminal and not through a Text Editor like vsCode. If it works, change the encoding of your script from UTF-8 to UTF-8 with BOM.
Some special characters are getting messed up when used in UTF-8 format, that’s why you need to use UTF-8 with BOM.
answered Mar 20 at 14:35
Summary:- >Many users encounter SQL Server Error 4064 while using the MS SQL database. This mistake occurs when you connect to a system that’s running Microsoft SQL Server, and you do not specify a login database, then the default database is used. So, this article suggests the best methods to solve this error related to the MSSQL database.
What is SQL Server Error 4064?
This error happens when the user database is set to the default database. When the user attempts to log in and find that the default database is dropped, then this error is shown as if the user “abcd” tries to log in to the database but fails as the database is set as default.
Lets us know the causes behind this type of error.
Valid Reasons for SQL Server error-
If the database is in suspect mode or the database is no longer exists. Then this blunder can take place. It is also possible that the SQL database has been set to the RESTRICTED_USER state. The database could be offline as well. If a user does not have the login account mapped, or the user has been denied access, then this error can also take place.
How to Get Rid of SQL Server Error 4064?
To get rid of the “Cannot open default user database login failed” error, different techniques need to be used depending on the server setting. There are manual methods explained below to solve database error. Then manual methods are only applicable for SQL Server 2005 and its later version. So, those users who are using the SQL server 2005 and its later version should follow the below methods.
There are two manual methods that quickly resolve this error:
- Use SQLCMD to Modify Default Database
- Use the GUI to Change Default Database
- Rectify Issues with Sysinfotools SQL Recovery
Method 1: Use SQLCMD to Modify Default Database
SQLCMD utility is used to modify the default database in SQL Server. So, to do this, follow the steps that are shown below:
- The first step is to click on Start and type Run in the search field.
- Then in the Run dialog box type cmd, and hit Enter.
- Deploy any of the techniques in the command prompt according to the sort of SQL Server login authentication you prefer:
- For Microsoft Windows authentication, Type this command “sqlcmd E -S InstanceName d master”
- For SQL Server authentication, Type this following command “Sqlcmd -S InstanceName -d master -U SQLLogin -P Password”
Then type the below-mentioned command in sqlcmd prompt and then hit the Enter key:
ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE= AvailDBName
Where AvailDBName shows the database name that already exists. This database can further be used by the login of SQL Server while synchronizing with SQL database.
Method 2: Use the GUI to Change Default Database
- First, open the SQL Server 2005 login page and then click on Options.
- Next, modify the Connect to database status with the help of existing databases such as Master.
- At last, click on connect.
Method 3: Solve Error with SQL Database Recovery
You can follow the above important methods one by one to solve the database error. If you are facing some issues with manual methods there is another best way to get rid of this issue is SQL Database Recovery.
This software recovers quickly helps in removing the SQL server error. Let us discuss some important features of SQL Data recovery:
- Recovers file objects including tables, stored procedures, views, programmability, triggers, default, and functions.
- Corrupt MDF and NDF files of SQL Server database can be repaired by this.
- It can save recovered data either in SQL Server database format.
- The preview option is there to review the data before saving.
Last words
Manual and a direct method has been explained above to move out from the SQL Server error 4064. Users can use any of the above methods but if someone wants to resolve the issue quickly can opt for the direct method. As you don’t need to identify the corruption of SQL data manually, this tool will automatically solve your issue and you can easily recover corrupted MySQL Database.
I have an SQL Azure database, and I’m able to connect to it using SQL Server Management Studio. When connecting to it, it displays
myServer.database.windows.net (SQL Server 12.0.2000.8 - myUser)
However, when I’m using the SQLCMD utility on the same machine, against the same SQL Azure server, with the same credentials, it always answers
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login failed for user 'myUser'
The syntax I’m using is
sqlcmd -S tcp:myServer.database.windows.net -U myUser@myServer -P "myPassword"
I tried everything I could think of:
- Added the port:
myServer.database.windows.net,1433 - Removed the
tcp:protocol - Enclosed the user in double quotes, like the password
- Tried the -I and -C command line options
- Combinations of the above.
The error is always the same.
Is there some specific flag that has to be activated for SQLCMD to work with SQL Azure? Any other ideas?