NAV 2013: How to log in any database

When you're a partner .. you deal with databases of your customers, right? At least - you should ;-). Usually, there is "some" security set up at the customers site. At least let's hope so ;-).

It might happen that you want to copy a customer's database to your site, just to test, debug, do extra development, test why that MRP won't run, do nasty things, .. you know .. do partner stuff.

What many of us do, is: take a backup of that database (or company) and restore it locally in the office. Problem is: there is security set up in that database, so there is no way to get in, even after a restore, we get this:

In NAV2009, we had the chance (at least in the native restore) to reset the security immediately after the restore, but in NAV2013, we first have to be able to log into the database in RTC to be able to set up security. So there's no obvious "bridge" anymore that we can take.. .

How do we force ourselves into that DB?

Your first guess would be .. thinking of NAV2013 and all the new great features in it …

Powershell!

That would be a safe bet, wouldn't it? In NAV2013, you have all these great new Cmdlets at your disposal …

And as you can see .. you can set up security. My guess would be: let's add myself into the system.

So, first, I look up my sid in a normal command shell (not powershell) with

wmic useraccount get name,sid

I copy the Sid I want, go back to Powershell, and do this:

New-NAVServerUser TestFBKRestore -sid MyCopiedSid

The result is somewhat disappointing .. but understandable:

You obviously need access to be able to add users.. .

What about SQL Server?

We all know the trick in the old days to reset security straight on SQL Server - deleting the content of certain tables... Well .. may be it's also possible in NAV2013.

I think it's safe to assume that the person who is trying to restore that database, is sysadmin on the SQL Server where it's restoring the database. Well, run this script on your new database:

delete from [dbo].[User]

delete from [dbo].[Access Control]

delete from [dbo].[User Property]

And you'll see you'll be able to get back in :-). In fact .. It's even better to clear a few more tables, just for deleting redundant data

delete from [dbo].[Page Data Personalization]

delete from [dbo].[User Default Style Sheet]

delete from [dbo].[User Metadata]

delete from [dbo].[User Personalization]

Hope it's useful for you.

And of course .. If you have comments or a better way .. I definitely want to know! Leave a comment :-).

Comment List
  • You can use the following statement to create a random User SID:

    SET @USERSID = NEWID()

  • glad you agree :-)

  • Hi Waldo,

    This is of course true, and let's be honest, without this possibility it would even be near to impossible to gain access in case you or the customer made a serious mistake.

    As long as the customer doesn't hand you over a copy of their live database, you won't gain access to it, so it's still the customer deciding if you will get access yes or no :-)

  • Thanks, Jeremy :-).  Much appreciated!

    @Peter,

    I still don't think it's a flaw.  In a way, you still need enough rights to gain access to the database.  You can secure that the same as any database .. even in NAV, you need an extra step (the given tables) to gain access, while in an orthodox database, SQL rights are enough.. .

    If I get a .BAK database in my hands of any database .. I can gain access to it even easier on my own server with admin rights.. .

    So I'm still not convinced why it would make NAV less secure then any other system..

  • I've long since had a saved script akin to Peter's, since I typically receive SQL BAK files, not FBK's, and there's no way into the SQL NAV database except scripting.  I generally do work on off-site upgrades a fair bit, so it also helps to leave things intact - just splice in what I need.

    Since Peter's script is super handy, and I imagine folks will land on this helpful blog post/discussion often, sharing a pre-2013 version:

    INSERT INTO [Windows Login] ([SID]) values ('[MyCopiedSid]');

    INSERT INTO [Windows Access Control] ([Login SID],[Role ID],[Company Name])

    values ('[MyCopiedSid]','SUPER','');

    I save that (with my SID) and then I can just point it against whatever BAK clients hand me.

    This also helps in the rare instance where a client's IT manager gives remote access and SQL admin powers, but they didn't realize they have to add me to their NAV security system as well.

  • Does make you wonder doesn't it? If it's so easy to gain access to a random database, how secure are your financials then? ;-)

  • Sure "one right way" doesn't exist. It always depends on the scenario. And for a local test copy or internal project then it's just as easy to to simply remove the users.

  • Eric,

    don't really agree.  In my opinion, there is not "one right way to go".  It just depends on the scenario.

    If I need a copy of the database for internal- or project reason, I don't need the users in the database .. in fact, having those users there don't make any sense ..

  • @Waldo, Peter already answered it. You can use SQL/NavUsers with NAV 2013. And the tip is also only useable if the SA is already in there, otherwise there is really nothing gained.

    And thanks @Peter, I have previously been using the same method as Eric specified above, but I think your script is the right way to go.

  • It's indeed a scenario where you don't want to clear out all existing users :-).

    I should start testing with that "ClientServicesCredentialType" .. thanks for the tip!

  • @Erik, Waldo":

    You could use SA as a database login, but you'd then have to:

    - Setup a service tier using NavUserPassword as ClientServicesCredentialType

    - Be sure this user is already existing as NAV user in the database (or add it using T-SQL)

    @Waldo: You'd need to use my script to add a user and leave the existing ones in if you're upgrading a customer database off-site

  • Erik,

    Well .. to get into NAV (Windows Client), you can't use database logins .. so SA is out of the question.. .  You can still log into the Development Environment with SA (I think - didn't test it...)

  • Erik,

    Well .. to get into NAV, you can't use database logins .. so SA is out of the question.. .  You can still log into the Development Environment with SA (I think - didn't test it...)

  • Peter,

    thanks for the script!

    Depends on the scenario in what is best to use, I guess ;)

  • And how does the old trick of always having a SA user (SQL user) in all NAV databases actually work on NAV 2013?

Related
Recommended