Tag Archives: sql

Postgres : Using integrated security on Windows on localhost

…is slightly less straightforward than you might hope, but helpfully more flexible. For MS SQL Server, integrated security implies that windows user are magically also SQL users and that no password or username is needed to login. But also, that you can no longer choose which user you login as. Postgres is more configurable and more complex. You can specify which users use SSPI and which postgres user(s) each windows user can login as. You can specify, for instance, that you are allowed to use SSPI to login as the postgres superuser.

Here is how you can login with integrated security, as the user postgres, whilst still being able to login as a different user with a password.

  1. Locate and open both your pg_hba.conf and pg_ident.conf files. I found them in C:\Program Files\PostgreSQL\data\pg96
  2. In pg_ident.conf add a line to map your windows login to the postgres user named postgres. You can also add other users. Here’s what my lines look like:
    # MAPNAME       SYSTEM-USERNAME         PG-USERNAME
    MapForSSPI     chris@MyMachineName    chris
    MapForSSPI     chris@MyMachineName    postgres

    (In normal unix style, the columns are separated by any amount of space or tab).

  3. In pg_hba.conf, add lines that allow user postgres to login with integrated security, whilst still allowing all other users to login with passwords. Again, you can add lines for other users too:
    # TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
    # IPv4 local & remote connections:
    host    all             postgres        127.0.0.1/32            sspi 	map=MapForSSPI
    host    all             chris           127.0.0.1/32            sspi 	map=MapForSSPI
    host    all             all             127.0.0.1/32            md5
    host    all             all             0.0.0.0/0               md5 
    # IPv6 local connections:
    host    all             postgres        ::1/128                 sspi 	map=MapForSSPI
    host    all             chris           ::1/128                 sspi 	map=MapForSSPI
    host    all             all             ::1/128                 md5
  4. Restart the Postgres service, for instance with a powershell command
    Restart-Service 'PostgreSQL 9.6 Server'
  5. Trying logging in as user postgres:
    • psql -h localhost -U postgres
  6. Trying logging in as some other user:
    • psql -h localhost -U someotherusercreatedwithcreaterole
      and you should be prompted for a password (unless you already mastered the pgpass.conf file)

Logging in without specifying a user name

You might expect that SSPI implies not having to specify a username. You would be wrong. Postgres still requires you specify a username when using SSPI, and, as above, allows you to choose which username.

You can however login without a username—with or without SSPI—simply by creating a postgres user (i.e., a role with LOGIN privilege) with your username. To let that user login with SSPI, you should still have a line in pg_ident.conf to map the SYSTEM-USERNAME yourname@yourmachinename to the postgres username yourname.

Integrated Security in .Net connection strings

Having done the above I can now use either of

"Host=localhost;Database=MyDb;Integrated Security=True;Username=postgres"
"Host=localhost;Database=MyDb;Integrated Security=True;Username=chris"

as a connection string for the npgsql Ado.Net driver

Reference

https://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html and subsequent pages on Authentication methods and the pg_ident.conf file.

Caveats

Why does the title of this post say ‘localhost’? Because SSPI only works on Windows; and only on either localhost, or a Windows Domain environment as per https://wiki.postgresql.org/wiki/Configuring_for_single_sign-on_using_SSPI_on_Windows

Postgres quick start for MS SQL / T-SQL developers

After 17 years on T-SQL I’m at last on my first project with Postgres. Here are the first things I needed for the transition:

  • Do this first: https://wiki.postgresql.org/wiki/First_steps as it answers most of your first questions about connections, databases, using roles for logins, users and groups, and so on.
  • 'String concatenation is done with ' || 'a double pipe '.
  • No NVarchar is needed, just varchar. The default installation uses UTF-8.
  • Date & Time: use timestamp for datetime/datetime2
  • Read https://www.postgresql.org/docs/9.1/static/datatype-datetime.html for more on dates, times and intervals.
  • Use more semicolon terminators If ... Then ... ; End If ;
  • plpqsql script is separated from “pure” SQL in a way that T-SQL just doesn’t bother with. If you are writing scripts, plpqsql must either be inside a function or inside an ‘anonymous function block’, usually delimited by $$ or $somestring$; and Declare variables in one block before Begin :
    Select 'This line is plain SQL' ;
    Do $$
    Declare
      anumber integer ;
      astring varchar(100);
    Begin
    If 1=1 Then
     Raise Notice 'This block is plpgsql' ;
    End If;
    End $$ ;
    Select 'This line is SQL again' ;
    

    In fact, functions are defined in a string, and the $$…$$ delimiters is a postgres syntax for specifying a string constant without having to escape any special characters at all. They can be used anywhere in code. Functions can be defined in other languages than plpgsql. See e.g. https://pgxn.org/dist/plv8/doc/plv8.html.

  • Variables: just use a plain identifier: myvariablename with no @ symbol or other decoration. BUT as a consequnce you must avoid variable names in a query that are the same as a column name in the same query. BUT BUT in Ado.Net Commands with Parameters, still use the @parametername syntax as you would for SQL Server
  • Use Function not procedure:
    Create or Replace Function procedureName( forId int, newName varchar(20) )
    Returns int -- use returns void for no return
    As $$
    Declare 
      localvariable int;
      othervariable varchar(10);
    Begin
     Insert into mytable (id,name) values (forId, newName) On Conflict (id) Do Update Set Name=NewName ;
    End $$;
  • Ooh, did you notice how postgres has a really simple upsert syntax?
  • Postgres does function overloads, so to drop a function you must give the signature: Drop function functionname(int)
  • Whereas the T-SQLer does everything in T-SQL, other database systems use non-sql commands for common tasks. For working at a command-line, learn about psql meta-commands.. Start with \c to change database, \l to list object, and \? to list other meta commands.
  • Postgres have (I think) made more effort than MS to stay close to the SQL standards and as with T-SQL, the docs go into detail on deviations. But this means that much of your code for databases, schemas, users, tables, views, etc can be translated fairly quickly.
  • Replace Identity with Serial. For more complex options, Postgres uses SQL sequences.
  • Replace Select top 1 ... with Select ... Fetch First 1 Only.
  • role covers all of logins (Create Role ... Login) and database users (Grant Connect on Database ... TO ...) and also covers groups.
  • The Nuget package for Ado.Net, including for .Net core, is Npgsql
  • Scan these

Converting T-SQL to Postgres

Here’s my initial search/replace list for converting existing code:

Search Replace
@variableName _VariableName
NVarChar VarChar
Identity Serial
UniqueIdentifier uuid
Raise Raise Exception
Print Raise Notice
Insert Into table Select … Insert into Table (COLNAMES) Select …
If Then Begin … End Else Begin … End If Then … ; Else … ; End If ;
Create Table #Name Create Temporary Table _Name
NewId() websearch Create Extension “uuid-ossp” and then use uuid_generate_v1() or uuid_generate_v4()
Alter Function|Procedure Create or Replace Function

Lost SQL Server sa password ? How to start up and login in single user mode

The problem: Someone has lost the sa admin password for your MS Sql Server; or the one person who has SQL admin rights has left the company. Alas, you find that even having Windows admin rights does not grant you access because you have a recent version of Sql Server and you didn’t grant Sql Server admin rights to the machine or domain admins.

You can still fix this. You will need local admin right on the machine, and the ability to:

  • open a command line as an administrator
  • look through the registry with RegEdit to find the settings for the version and instance of Sql Server you are locked out of. MSDN mssqlserverloginmode-registry-key has some clues.
  • look through Program Files\Microsoft SQL Server\ and find the binn directory for your version and instance of Sql Server.

The trick is to start Sql Server in single user mode, and then login as a local admin. This will give you admin access to the SQl Server.

How to Get Admin Access to Sql Server on Your Machine

  1. Stop the sql service.
    • This is most easily done via the Windows Services Gui, but net stop MSSQLSERVER might do it. If you have a named instance use net stop MSSQL$instancename
  2. Work out the file location and registry key for the version/instance name of sql server you are trying to get into. This may be trickier than you think – you may have SqlExpress as well as more than one version and instance name of MSSQLServer. For instance:
    • C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn and
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\

    or

    • C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn and
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS
  3. Change the registry entry for loginmode to 2 (not 0 or 1), which enables both Windows and SQL authentication.
  4. Open a command line window as administrator and navigate to the binn directory you found earlier under C:\Program Files\Microsoft SQL Server\. Run sql from the command line using the –f parameter, sqlservr.exe -f
    • You may need more command line parameters to get your instance running properly, although I never have so far. If so, use the Windows Services Gui to see what the rest of your command line has to be.
    • For a named instance, your command line is sqlserver.exe -f -s instancename
    • An alternative to -f is -m, but -f worked for me.
  5. Open another commandline, also as administrator, and run sqlcmd –S <servername>. Sqlcmd is usually on the path, but if not it should be in the same directory as sqlservr.exe.
    • The server name for local machine is of course ‘.‘, as in sqlcmd -S .
  6. Now you can type T-SQL commands. Try Select @@ServerName, @@Version just for fun.
  7. Note that after typing your commands you must type GO and enter before anything you’ve typed gets sent to the server.
  8. Add yourself to the sysadmin role:
    EXEC sp_addsrvrolemember 'DomainName\LoginName', 'sysadmin'
  9. Or, enable the sa login and set the password with 2 lines of T-Sql:
    Alter login sa With Password= '<enterStrongPasswordHere>'
    Alter login sa Enable
    Go
  10. Exit and close both command windows.
  11. Restart the Sql Server service from the services Gui or with net start MSSQLSERVER or net start MSSQL$instancename

Done.

sp_WhoLock – a T-SQL stored proc combining sp_who and sp_lock to show who’s locking what and how much

Run this query, or remove the comments to actually create a stored proc.
Note that SQL pre-2008 requires a field to be commented out of the query, as noted inline.

/*
--Create Procedure WhoLock
--AS

if object_id('tempdb..#locksummary') is not null Drop table #locksummary
if object_id('tempdb..#lock') is not null Drop table #lock
create table #lock (    spid int,    dbid int,    objId int,    indId int,    Type char(4),    resource nchar(32),    Mode char(8),    status char(6))
Insert into #lock exec sp_lock
if object_id('tempdb..#who') is not null Drop table #who
create table #who (     spid int, ecid int, status char(30),
            loginame char(128), hostname char(128),
            blk char(5), dbname char(128), cmd char(16)
            --
            , request_id INT --Remove this line for SQL 2005 or earlier
            --
         )
Insert into #who exec sp_who
Print '-----------------------------------------'
Print 'Lock Summary for ' + @@servername  + ' (excluding tempdb):'
Print '-----------------------------------------' + Char(10)
Select     left(loginame, 28) as loginame, 
    left(db_name(dbid),128) as DB,
    left(object_name(objID),30) as object,
    max(mode) as [ToLevel],
    Count(*) as [How Many],
    Max(Case When mode= 'X' Then cmd Else null End) as [Xclusive lock for command],
    l.spid, hostname
into #LockSummary
from #lock l join #who w on l.spid= w.spid
where dbID != db_id('tempdb') and l.status='GRANT'
group by dbID, objID, l.spid, hostname, loginame

Select * from #LockSummary order by [ToLevel] Desc, [How Many] Desc, loginame, DB, object

Print '--------'
Print 'Who is blocking:'
Print '--------' + char(10)
SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, p.loginame
, convert(char(12), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM      master..sysprocesses p
JOIN      master..sysdatabases d ON p.dbid =  d.dbid
WHERE     EXISTS (  SELECT 1
          FROM      master..sysprocesses p2
          WHERE     p2.blocked = p.spid )

Print '--------'
Print 'Details:'
Print '--------' + char(10)
Select     left(loginame, 30) as loginame,  l.spid,
    left(db_name(dbid),15) as DB,
    left(object_name(objID),40) as object,
    mode ,
    blk,
    l.status
from #lock l join #who w on l.spid= w.spid
where dbID != db_id('tempdb') and blk <>0
Order by mode desc, blk, loginame, dbID, objID, l.status

Although this is a bare metal approach to investigating sql contention, the query does show you actual login names, so it enables you to join up technical issues with affected/affecting customers.