December 24, 2010 at 8:35 am
Hi All,
I'm having a issue adding a new login only if I set it to SQL Server Authentication and I set the password. I'm trying to add a login that was created on our domain with admin security rights. Info:
New User ID = FREEDOM\SqlAgentWorker
I get the following error
TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for Login 'FREEDOM\SqlAgentWorker'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
'FREEDOM\SqlAgentWorker' is not a valid name because it contains invalid characters. (Microsoft SQL Server, Error: 15006)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=15006&LinkId=20476
Please Note: If I add the user Id using Windows Authentication it works without any issues. I have not tried to remove the FREEDOM\ part, but I thought that it would be needed for proper identification. Since I could not get it to work right in SQL Management Studio, I tried the following command:
CREATE LOGIN [FREEDOM\SqlAgentWorke] from WINDOWS WITH PASSWORD='91464502599'
GO
Questions:
A. Can I use "From Windows" and "with PASSWORD" in the same command.
If not, How would I set the password, or does SQL Server use the password that was assigned when the domain account was created.
Thanks for any suggestions.
December 24, 2010 at 8:37 am
BTW, I added this account to work with SQL Server Agent between some Linked servers I have.
December 24, 2010 at 9:40 am
For Logins from Windows accounts, no options may be specified, hence the somewhat misleading message. Correct is
CREATE LOGIN [FREEDOM\SqlAgentWorke] from WINDOWS
go
SQL = Scarcely Qualifies as a Language
December 24, 2010 at 11:39 am
Hi Carl,
Thanks for the reply. Now I have an issue with the SQL Server Agent not running. I have assigned the new user id to the SQL Agent Job (by adding the user id to the Owner field of the Job properties. "Run As" is empty. When I run this job I get the following error message from the error logs:
Date12/24/2010 1:21:10 PM
LogSQL Server (Current - 12/24/2010 1:21:00 PM)
SourceLogon
Message
Error: 18456, Severity: 14, State: 11.
I have given this new ID sysadmin Server Role. Actually I selected all the items in the Server roles box to make sure it would have the needed rights. May be overboard, but willing to cut some of those roles once I'm sure what works.
The new User ID is located on the Linked server and the main server I'm trying to access from the SQL Agent Job.
In the Linked server properties, I have added the User Id to the Security section under "Local Server login to remote server login mappings" and Impersonate is checked (No remote user or Remote Password). I didn't add any password because I'm not sure if SQL Server will recognize the Password setup for the domain account.
Do you have any suggestions?
Thanks again for you first reply. I was not sure if I'd get a reply today being its Christmas Eve. Happy Holidays.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply