• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 581
  • Last Modified:

SQL error while adding login to SHAREPOINT instance

Hello Experts,
I encounter an error while executing the solution provided by ACH1LLES (which worked fine on a VM, thanks again ACH1LLES;) to get sysadmin permission to SHAREPOINT instance on another SBS 2011 Server :
solution link
sqlcmd -S.\SHAREPOINT
1> USE [master]
2> GO
Changed database context to 'master'.
1> CREATE LOGIN [DOMAIN\Domain Admins] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
2> GO
1> EXEC master..sp_addsrvrolemember @loginame = N'DOMAIN\Domain Admins', @rolename = N'sysadmin'
2> GO
Msg 102, Level 15, State 1, Server SRV01\SHAREPOINT, Line 1
Incorrect syntax near '''.


Any idea ?
0
jet-info
Asked:
jet-info
  • 12
  • 10
1 Solution
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
sqlcmd -S.\SHAREPOINT
1> USE [master]
2> GO
Changed database context to 'master'.

Whas this an example, or does it fail further down?
0
 
jet-infoAuthor Commented:
The fist part seems to be OK :

sqlcmd -S.\SHAREPOINT
1> USE [master]
2> GO
Changed database context to 'master'. //That is normal, it works fine
1> CREATE LOGIN [DOMAIN\Domain Admins] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
2> GO

That command makes an error :

1> EXEC master..sp_addsrvrolemember @loginame = N'DOMAIN\Domain Admins', @rolename = N'sysadmin'
2> GO
Msg 102, Level 15, State 1, Server SRV01\SHAREPOINT, Line 1
Incorrect syntax near '''.
// That is the error I have to fix, but I don't understand what is going on, please help.

Where to find the log to identify the error ?

Thank you for your help !
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
You could replace the Domain admins with local administrators like
SRV01\Administrators
Replace DOMAIN\Domain Admins with the above and ANY local admin will be a sysadmin in the SQL

Regards Marten
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
jet-infoAuthor Commented:
I'd already tried that but it doesn't work with any credential.
I think that the error is somewhere else.

Where can I find the log or the script with the error at line 1 ?
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Then the problem is like this:
The credential running SQLCMD is not a sysadmin itself!
Then it cannot grant someone else (a newly created account) sysadmin rights.

So, can you restart your server?

This is what you do, you start it with the -m switch. Then it is in single user mode and local admins ARE sysadmin.
http://msdn.microsoft.com/en-us/library/dd207004(v=sql.105).aspx

How to start SQL Server in a CMD window:
http://msdn.microsoft.com/en-us/library/ms180965(v=sql.105).aspx
Note the degraded performance if CMD is minimized

How to use -m when starting SQL Server
http://msdn.microsoft.com/en-us/library/ms188236(v=sql.105).aspx
Note, dont start SQL Agent, or it will "steal" the connection, and consider using the
-m "sqlcmd" in order to limit connections to only sqlcmd program.
Also note how to direct what instance youre starting using -s <instancename>.

Once it's started like this, given you are a local admin, your script will work with the
SRV01\Administrators account. It should also work with Domain Admins, given you change the DOMAIN\Domain Admins part to fit your chosen domain name.

Regards Marten
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Clarification: "So, can you restart your server?", I am referring to the SQL Service Server, not the host OS. I e, is it ok to restart your SQL Services?

Regards Marten
0
 
jet-infoAuthor Commented:
I try that tomorrow when I'll be on site, I let you know the result.

Thank you for your help !
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Good, I'll be here.
Please read the links I provided so you understand what options you have, especially the:
-m "sqlcmd"
switch in the http://msdn.microsoft.com/en-us/library/ms188236(v=sql.105).aspx

Regards Marten
0
 
jet-infoAuthor Commented:
So I restarted the server, I stopped the SHAREPOINT instance (the one I want to take control of), I add ;-m"SQLCMD" at the end of the startup parameters. I start the instance, I open a cmd as admin and launch :
sqlcmd -S.\SHAREPOINT

here is what I get :
C:\Windows\system32>sqlcmd -S.\SHAREPOINT
Msg 18461, Level 14, State 1, Server SRV01\SHAREPOINT, Line 1
Login failed for user 'DMN\admin'. Reason: Server is in single user mode. Onl
y one administrator can connect at this time.


When I add ;-mSQLCMD without the quotes I can connect to the instance but I get the error above... I don't understand because on my VM a could run the script successfully without the quote in the startup parameters...  Maybe a Microsoft update that wasn't present on the VM...?

I tried to activate the DMN\administrator user (which was disabled by SBS) but it is the same result, I cannot connect.
I cannot log in with local "administrator" user because the server is a DC, but all the users above are local admins too.

The lines "Login failed for user 'DMN\admin'. Reason: Server is in single user mode. Onl
y one administrator can connect at this time." let me think that an admin is already connected...?

Regards

PS : Excuse my English please.
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Did you read the link about NOT starting SQL Agent srrvices, or it will steal the only availiable connection?

Regards Marten
0
 
jet-infoAuthor Commented:
Yes absolutely, none SQL agent is started, I have two SQL Agent in the services list (SHAREPOINT and SBSMONITORING) and both are stopped...

I tried to stop all SQL related services, edited the statup parameters for the SHAREPOINT instance, started that instance and tried with no success to connect to it with sqlcmd...

I tried to connect with all admins credentials possible with the runas cmd command...

edit : The error for the last command looks like a syntax error, maybe the problem is there ?

1> EXEC master..sp_addsrvrolemember @loginame = N'DMN\Administrator', @rolena
me = N'sysadmin'
2> GO
Msg 102, Level 15, State 1, Server SRV01\SHAREPOINT, Line 1
Incorrect syntax near '''.


Where to check the line 1 please ?
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
NONONO
if you start with the -m switch. A local admin will be granted sysadmin priviledges.
So any local admin cred is used to start the SQLCMD. then use the -E to use the credentials that started the cmd prompt.
Now you have problem because something is using the sql. It should not be able to since you specify -mSQLCMD.

Try, as a local administrator in a cmd prompt, after starting with -mSQLCMD
SQLCMD -S.\SHAREPOINT -E

This should work

Regards Marten
0
 
jet-infoAuthor Commented:
I am sorry Marten,

I tried on another SBS 2011 std server of another customer to see if I am crazy or not... and it works perfectly !

I think that there is something else on that server... I am not familiar with SQL Server, I don't know what to looking for now...

Regards
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
When starting the way I described, do you get a connection. Can you type
Select @@version
And paste result here.
0
 
jet-infoAuthor Commented:
Here is the result :

C:\>sqlcmd -S.\SHAREPOINT -E
1> USE [master]
2> GO
Changed database context to 'master'.
1> EXEC master..sp_addsrvrolemember @loginame = N'DMN\admin', @rolename = N'sysadmin'
2> GO
Msg 102, Level 15, State 1, Server INOSRV01\SHAREPOINT, Line 1
Incorrect syntax near '''.
1> Select @@version
2> go




--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
        Jun 17 2011 00:54:03
        Copyright (c) Microsoft Corporation
        Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64>
(Build 7601: Service Pack 1)



(1 rows affected)
1>

Thank you for your help and your patience Marten !
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Now lets take it step by step.
List all accounts you're allowed to see:

USE Master
GO
select name from syslogins

Do you see the account DMN\Admin in there. Respond swiftly, I'll have an Eye open on this thread!

Regards Marten
0
 
jet-infoAuthor Commented:
Yes, it is there.
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
OK I propose yóu use a SQL Account, just in case the AD has anything to do with it.
Run these lines the same way you ran the other, i e with -m switch

USE [master]
GO
CREATE LOGIN [TempSA] WITH PASSWORD=N'passwordabc123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'TempSA', @rolename = N'sysadmin'
GO
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
I successfully run, you should start SQL normally, and connect using the TempSA account.
Feel free to use another password.

Once youre connected you can troubleshoot the accounts with the SSMS Gui. Wich makes things a lot simpler.

Regards Marten
0
 
jet-infoAuthor Commented:
OK, it's working fine with the SQL credential !

Thank you so much Marten ! You deserve more than 500 points for it !
Please be aware that you have all my gratitude anyway !

Thanks again Marten !
0
 
jet-infoAuthor Commented:
Thank you for your patience and your knowledge !
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Glad to see it resolved

Regards Marten
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now