Solved

SQL error while adding login to SHAREPOINT instance

Posted on 2013-01-14
22
547 Views
Last Modified: 2013-01-18
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
Comment
Question by:jet-info
  • 12
  • 10
22 Comments
 
LVL 20

Expert Comment

by:Marten Rune
ID: 38777126
sqlcmd -S.\SHAREPOINT
1> USE [master]
2> GO
Changed database context to 'master'.

Whas this an example, or does it fail further down?
0
 

Author Comment

by:jet-info
ID: 38777184
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
 
LVL 20

Expert Comment

by:Marten Rune
ID: 38777223
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
 

Author Comment

by:jet-info
ID: 38777312
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
 
LVL 20

Expert Comment

by:Marten Rune
ID: 38777375
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
 
LVL 20

Expert Comment

by:Marten Rune
ID: 38777510
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
 

Author Comment

by:jet-info
ID: 38782443
I try that tomorrow when I'll be on site, I let you know the result.

Thank you for your help !
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 38782484
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
 

Author Comment

by:jet-info
ID: 38786122
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
 
LVL 20

Expert Comment

by:Marten Rune
ID: 38786330
Did you read the link about NOT starting SQL Agent srrvices, or it will steal the only availiable connection?

Regards Marten
0
 

Author Comment

by:jet-info
ID: 38786598
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 20

Expert Comment

by:Marten Rune
ID: 38786672
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
 

Author Comment

by:jet-info
ID: 38787263
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
 
LVL 20

Expert Comment

by:Marten Rune
ID: 38788276
When starting the way I described, do you get a connection. Can you type
Select @@version
And paste result here.
0
 

Author Comment

by:jet-info
ID: 38792020
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
 
LVL 20

Expert Comment

by:Marten Rune
ID: 38792029
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
 

Author Comment

by:jet-info
ID: 38792134
Yes, it is there.
0
 
LVL 20

Accepted Solution

by:
Marten Rune earned 500 total points
ID: 38792247
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
 
LVL 20

Expert Comment

by:Marten Rune
ID: 38792249
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
 

Author Comment

by:jet-info
ID: 38792357
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
 

Author Closing Comment

by:jet-info
ID: 38792361
Thank you for your patience and your knowledge !
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 38792505
Glad to see it resolved

Regards Marten
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now