SQL error while adding login to SHAREPOINT instance

sqlcmd -S.\SHAREPOINT
1> USE [master]
2> GO
Changed database context to 'master'.

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

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 !

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

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 ?

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

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

I try that tomorrow when I'll be on site, I let you know the result.

Thank you for your help !

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

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.

Did you read the link about NOT starting SQL Agent srrvices, or it will steal the only availiable connection?

Regards Marten

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 ?

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

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

When starting the way I described, do you get a connection. Can you type
Select @@version
And paste result here.

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 !

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

Yes, it is there.

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

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 !

Thank you for your patience and your knowledge !

Glad to see it resolved

Regards Marten