Hot answers tagged

124

There is another way, which I now use in preference to the runas /netonly method. You can add the credentials to your profile in Windows using the Credential Manager found in the Windows control panel. Open Credential Manager Click "Add A Windows Credential" Populate the "internet or network address" field with the name and port number ...


83

You are attempting to pass Windows credentials in plain text from the connection string of an application. This simply isn't how Windows authentication works, and largely defeats the purpose. You also can't just create the same username with the same password in your own domain, and expect that to magically work. Domain name is still part of the validation -...


33

This means that the login [R2Server\AAOUser] is already mapped to a user in that database. Or, in other words, another database user is using this login. You can see what database user is using your login with the following query: use YourDB go SELECT su.name as DatabaseUser FROM sys.sysusers su join sys.syslogins sl on sl.sid = su.sid where sl.name = 'test'...


26

State codes and their meaning. 1 'Account is locked out' 2 'User id is not valid' 3-4 'Undocumented' 5 'User id is not valid' 6 'Undocumented' 7 'The login being used is disabled' 8 'Incorrect password' 9 'Invalid password' 10 'Related to a SQL login being bound to Windows domain password policy enforcement. ...


21

No. While the documentation currently has the following arguably ambiguous statement about what this flag means: Password policy is checked. What it really means, and should say, is that the flag serves two purposes: The password policy might have been checked, but only if (a) the password policy was enabled at the time the password was last set, ...


18

USE msdb; GO CREATE USER shims FROM LOGIN shims; GO ALTER ROLE SqlAgentUserRole ADD MEMBER shims; GO Also, for future reference, any time you know how to do something in the UI but not in a script, this is what the Script option on most dialogs is for - it will show you what script SSMS would have executed:


18

You can check it in the registry: HKLM\Software\Microsoft\Microsoft SQL Server\ [instancename] \MSSqlServer The current mode is recorded in the LoginMode key. Values (DWORD) can be: 1 = Windows Authentication mode 2 = SQL Server and Windows Authentication mode Note: 0 is equivalent to 2 You still need access to the Registry (and the server)


15

My understanding is that if you aren't using Contained Databases, you will have to ensure logins are created on other instances manually. Something like this script from SQLSoldier, originally posted as Transferring Logins to a Database Mirror, should do the trick.


15

My answer... From my comments: The issue was that the server was set to "Windows Authentication Mode" only. To fix this I Right click the server - > Properties Click "Security" in the left side of the "Server Properties" dialog Changed server Authentication to "SQL Server and Windows Authentication mode" Clicked "OK" Restarted Associated services. At ...


15

Here's one way using dynamic SQL. There's not really any way to do this without iterating but this approach is much safer than undocumented, unsupported and buggy options like sp_MSforeachdb. This will get a list of all online databases, the mapped user (if it exists) along with the default schema name and a comma-separated list of the roles they belong to. ...


15

Another approach is to try logging in, using SQL authentication, with an obviously fake account that won't succeed. You can then use notepad to open up the ERRORLOG file in C:\Program Files\Microsoft SQL Server\$instance folder$\MSSQL\Log\ and see either this error... Error: 18456, Severity: 14, State: 58. Login failed for user 'polly_wants_a_cracker'. ...


14

I think there is no reliable solution for your problem since Application Name is modifiable parameter that cam be changed by any user. Here is how to change it within SSMS: In Connect to Database Object dialog choose Options, open Additional Connection Parameters and choose any name for Application Name like this: Now sys.dm_exec_sessions DMV and ...


13

Did you look anywhere? Right-click the server and choose Properties, then select the Security screen. Then, log in using your Windows auth admin account, and set the password for the sa SQL auth account. ALTER LOGIN sa WITH PASSWORD = 'strong password here';


12

You can use a server logon trigger to make custom logon validations and reject them whenever you see fit. You will see this trigger listed below "Server Objects" and inside "Triggers" if you are using SSMS. For example: CREATE TRIGGER strRejectSSMSConnectionForSQLLogin1 ON ALL SERVER FOR LOGON AS BEGIN IF ORIGINAL_LOGIN() = N'SQLLogin1' AND ...


11

My article will help if you set it up in advance, but not when the event happened in the past and you didn't have any kind of auditing mechanism set up. There is still hope, though. Let's say I did this: CREATE LOGIN flooberella WITH PASSWORD = N'x', CHECK_POLICY = OFF; This information is in the default trace under EventClass 104 (Audit Addlogin Event). ...


11

In pg_authid By default encrypted, see here no, not possible (if encrypted)


10

Yes, you can but you have to set everything up. SQL Server (SQL) Login When you enable the Enforce password policy option in the SQL Server Login window, you are basically telling the SQL Server to adhere either to the local security policy or to the policy defined in the domain. The enforcement of password policy can be configured separately for each ...


9

A user that was explicitly created with WITHOUT LOGIN cannot be mapped to a login after the fact. A user that was created with a login and then lost its association, e.g. because the database was moved to another server, can be remapped by first creating a new login (any name) and then using the ALTER USER ... WITH LOGIN = ... command. To demonstrate I am ...


9

This login is used for Extended Events. More information here: Extended Events logins I disabled it once, and I was no longer able to collect the data I needed. If I were you I'd keep the login as it is, unless of course you already know that you wont need tracing/Extended Events. When it comes to telemetry you may like to read the instructions how it can ...


8

You should be able to do something like this: EXECUTE AS login = 'loginname'; SELECT name FROM sys.databases WHERE HAS_DBACCESS(name) = 1; REVERT; For reference see EXECUTE AS and HAS_DBACCESS


8

You need to create the login on the DB2 server. Use Robert's script so that it grabs the sid and password, well if it's a SQL account that is. Then grant the permission on DB1. That permission will auto-magically get copied over to DB2 due to the AG. There is no way to just grant permission on DB2. It has to be done on DB1. You can control access by ...


8

No, you can't feed a query to an ALTER LOGIN command. But you can build the command pretty easily using dynamic SQL: DECLARE @sql nvarchar(max) = N'', @cmd nvarchar(550) = N'ALTER LOGIN $$$ DISABLE;' + CHAR(13) + CHAR(10); SELECT @sql += REPLACE(@cmd, N'$$$', QUOTENAME(userid)) FROM dbo.tbl_user; PRINT @sql; -- EXEC ...


7

What you will have to do is connect to the instance that has a mis-matched SID, and you'll have to recreate the login and specify an explicit SID. For instance, on the instance where you have the orphaned user and the following returns the user: exec sp_change_users_login 'report'; go Copy the SID from the column UserSID. And if you already have an ...


7

You must either use a Contained Database, or you must recreate the users on the other server(s) with the same password hash and SID. A script to do this is provided by Microsoft: How to transfer logins and passwords between instances of SQL Server Mark's solution was partially right however his recommended solution was for Mirrored databases, as opposed to ...


7

This script is slightly modified from a script mentioned at will do what you are looking for. Replace 'ThursdayClass' with the login you need info for. https://www.simple-talk.com/sql/sql-tools/the-sqlcmd-workbench/ SET NOCOUNT ON CREATE TABLE #temp ( SERVER_name SYSNAME NULL , Database_name SYSNAME NULL , ...


7

Is the TCP port SQL Server is listening on open globally? If so, yes I'd be concerned. If there is a password that can be brute-forced or guessed, or an exploit that allows someone to bypass authentication, eventually your database could be compromised. You'll also be vulnerable to attacks that don't require access, such as someone filling up the drive that ...


7

No, if the user connects to SQL Server using a SQL Authentication login, there is no way to determine from that which Windows login was responsible. SQL Server can only record the information it has been provided, and when you use SQL Authentication, no Windows login / domain / group information is passed to SQL Server. You can look at DMVs like sys....


7

I found the issue myself. I will document it in case anyone else is facing the issue in future. The following user definition: CREATE USER [Angela] WITHOUT LOGIN WITH DEFAULT_SCHEMA = [Angela]; created the following code in the model.xml that avoids publishing, maybe because the schema (Angela) used there is invalid because it simply does not exist: &...


7

If the application is setting the language on the session when it logs in, then the default language doesn't matter. If it's not specifically setting the language on the session, then there is risk of date strings being converted incorrectly, and dates being returned in American English format instead of British English. Unless there is some unstated ...


Only top voted, non community-wiki answers of a minimum length are eligible