I can't get impersonation to work !

Paulm · Post by **Paulm** » Thu Feb 10, 2005 11:04 am

Same error

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'sp_grantlogin'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'sp_grantdbaccess'.

Paulm · Post by **Paulm** » Thu Feb 10, 2005 11:06 am

Ran the script as below:

IF ( NOT EXISTS (SELECT sid FROM master..syslogins WHERE name = 'LIONTRUST\SGVault'))
EXEC sp_grantlogin 'LIONTRUST\SGVault'
GO

USE sgvault
GO

IF ( NOT EXISTS (SELECT * from sgvault.dbo.sysusers WHERE name = 'LIONTRUST\SGVault'))
EXEC sp_grantdbaccess 'LIONTRUST\SGVault' , 'LIONTRUST\SGVault'

EXEC sp_addrolemember 'db_owner', 'LIONTRUST\SGVault'
EXEC sp_addrolemember 'public', 'LIONTRUST\SGVault'
GO

---------

Got message back:

'LIONTRUST\SGVault' added to role 'db_owner'.
Server: Msg 15081, Level 16, State 1, Procedure sp_addrolemember, Line 26
Membership of the public role cannot be changed.

Post by **jclausius** » Thu Feb 10, 2005 11:10 am

So at this point, we know SGVault has access to SQL Server, as well as access to the sgvault database. Additionally, it has both db_owner as well as public role members.

Let's give this one a try. Try to log in with Vault, Vault Admin, or Vault web. If there is some type of error, let's take a look at what the server log is now telling us.

Paulm · Post by **Paulm** » Thu Feb 10, 2005 11:14 am

Used Admin Tool from my client PC

The Vault Server could not establish a connection to the database. Please check the server's log for detailed information. (FailDBConn)

Here is the vault log.

System Started
Version 3.0.2.2812
Cache Level = 1
DataBase Buffer Size (KB) = 256
LogFile Path = C:\WINDOWS\Temp\sgvault
Log Level = Quiet
Archive Log = Weekly
ReverseDNS Lookup = True
Maximum HTTP Request Length = 102400
Overwrite Log on Startup = False
Session Timeout = 4320
Active Directory Domain =
SGVault Working Directory = C:\WINDOWS\Temp
SGVault Server URL = http://puma.plc.liontrust.co.uk/VaultService
Identity = LIONTRUST\SGVault
----10/02/2005 16:46:28 --()-- Vault Failed to establish a connection to the database.
----10/02/2005 16:46:28 --()-- Login failed for user 'LIONTRUST\SGVault'.
----10/02/2005 16:46:28 --()-- Vault Failed to establish a connection to the database.
----10/02/2005 16:46:28 --()-- Login failed for user 'LIONTRUST\SGVault'.
----10/02/2005 16:46:28 --()-- The session information was not removed from the database. Please check sgvault.dbo.tblsessions within SQL Server. There may be orphaned sessions stored within the database. Error: FailDBConn
----10/02/2005 16:46:35 --192.168.0.204(192.168.0.204)--SSL Disabled Vault Failed to establish a connection to the database.
----10/02/2005 16:46:35 --192.168.0.204(192.168.0.204)--SSL Disabled Login failed for user 'LIONTRUST\SGVault'.
----10/02/2005 16:46:36 admin--192.168.0.204(192.168.0.204)--SSL Disabled Vault Failed to establish a connection to the database.
----10/02/2005 16:46:36 admin--192.168.0.204(192.168.0.204)--SSL Disabled Login failed for user 'LIONTRUST\SGVault'.
----10/02/2005 17:12:31 --192.168.0.204(192.168.0.204)--SSL Disabled Vault Failed to establish a connection to the database.
----10/02/2005 17:12:31 --192.168.0.204(192.168.0.204)--SSL Disabled Login failed for user 'LIONTRUST\SGVault'.
----10/02/2005 17:12:31 admin--192.168.0.204(192.168.0.204)--SSL Disabled Vault Failed to establish a connection to the database.
----10/02/2005 17:12:31 admin--192.168.0.204(192.168.0.204)--SSL Disabled Login failed for user 'LIONTRUST\SGVault'.

Post by **jclausius** » Thu Feb 10, 2005 11:18 am

Are the Vault Server / SQL Server on the same machine? Are all machines on LIONTRUST domain?

Paulm · Post by **Paulm** » Thu Feb 10, 2005 11:19 am

Yes, Vault and the SQl Server 2000 is on the same machine. It is a windows 2003 member server in the liontrust (plc.liontrust.co.uk) domain.

Paulm · Post by **Paulm** » Thu Feb 10, 2005 11:20 am

All servers are on the same network, not seperated by routers / firewalls etc.

Post by **jclausius** » Thu Feb 10, 2005 11:37 am

Scratching my head here.

Some other things come to mind:

Perhaps the connection is not going to the correct database. Can you examine your web.config within the VaultService directory. Look at the <add "ConnectString" > xml element. Does the server name match the name of the SQL Server?

Assuming that matches, let's try to to log into the database manually. You will need the connect string's server value.

-Can you login as SGVault on the Vault server
-Open a command prompt
-Run "osql -S PLACE_SERVERNAME_FROM_CONNECT_STRING -E"
-If this lets you in, you should see a 1>. Run "SELECT schemaversion FROM sgvault.dbo.tblschemaversions;"
-Hit Enter
-At 2>, type "GO" and hit Enter
-If these run correctly, you will get a schema version number.
-Type "exit" to quit

Does this run correctly?

Paulm · Post by **Paulm** » Thu Feb 10, 2005 11:54 am

<add key="ConnectString" value="Application Name='SourceGear Vault Server'; Connection Reset='false'; Server=PUMA; Database=sgvault; Integrated Security='true'" />

This looks ok, Puma is the name of the server. Or should it be fully qualified as puma.plc.liontrust.co.uk ?

OK I logged into the server as Liontrust\SGVault and tried running the command osql -S puma -E

I get login failed for user 'Liontrust\SGVault'

Post by **jclausius** » Thu Feb 10, 2005 12:09 pm

We're getting somewhere...

We need to determine why LIONTRUST\SGVault cannot log in.

Do you happen to know what collation the database was installed with? If it was using a case sensitive collation, I believe Liontrust\SGVault is a different user than LIONTRUST\SGVault.

Since this is SQL Server, maybe we should try to use Enterprise Manager to do things.

1) Log back in to an account which has Administrative access to SQL Server
2) From within Enterprise Manager, locate the sgvault database. Under this database, there will be a list of users. Do you see Liontrust\SGVault?
3) If you do see Liontrust\SGVault, delete it.
4) Now moving to the Security\Logins node of the tree, you should see which Windows accounts have access to SQL Server. Do you see Liontrust\SGVault?
5) If you do see Liontrust\SGVault, delete it.

Assuming things are removed correctly, let's re-grant permissions
1) In the Security\Logins node, invoke "New Login..." Click the ellipsis button, and find Liontrust\SGVault. Grant them access, and make the sgvault database their "default database". Don't hit OK just yet.
2) Switch to the Database access tab. Ensure the sgvault database as been given permissions. Also ensure public and db_owner have also been checked.
3) Hit OK.

Did this work? If so, log out, log back in as SGVault and try the same osql command. Does it succeed this time?

Paulm · Post by **Paulm** » Thu Feb 10, 2005 12:15 pm

Whoppeee it works now ! It was the SGVault SQL login after all. Deleted the login from the database and the security container. Re-ran your script and it created the login again. Now tried connecting using the Admin tool and it works !
STOP PRESS --> Just got your reply thanks, thats exactly what I did but used your script instead. Worth noting for future installs and save a lot of hair pulling.

Thanks a lot guys...I can now test this software!!

Post by **jclausius** » Thu Feb 10, 2005 12:26 pm

Sorry you had to go through so much effort. The installer will handle these things for you ( I believe Identity switcher is supposed to do this as well ). It could have been there was some sort of problem with the information currently in the database, that it could not be removed for one reason or another - causing the conflict.

In any case, I'm glad we were able to work together to fix the problem.