Error when deleting context (after using custom container with builtin user and adding a new context for it

[SQLDBAWithABeard]

I don't know if this is - As a user I would like to be able to edit the context to use a different user!

or

As a user I want to be able to run sqlcmd create mssql for containers with custom users and add the user to the context!

With that in mind, this is what I did.

I am finishing my demo for Data Ceili and wanted to show that you can use a custom container. So I decided to use our dbachecks one which has a custom user.

My config is blank

PS > sqlcmd config view
version: v1
endpoints: []
contexts: []
currentcontext: ""
users: []

so I create a container like so - I use verbosity 4 so I can see the log and see when I can CTRL C safely

# what about if we wanted to create a container from our own image ?
# and define the context name

sqlcmd create mssql --name dbachecks --hostname William  --accept-eula --cached --context-name dbachecks1 --registry dbachecks --repo sqlinstance1 --tag v2.36.0  --verbosity 4

# now we have a custom user in this container so the context wont work and I have to CTRL C out

now that has run, my config looks like

PS > sqlcmd config view
version: v1
endpoints:
- asset:
    container:
      id: 0c840ce5ee2f471ae6ede2747dc25d669f9952ad46effbc2d50a7849d063469e
      image: dbachecks/sqlinstance1:v2.36.0
  endpoint:
    address: 127.0.0.1
    port: 1433
  name: dbachecks1
contexts:
- context:
    endpoint: dbachecks1
    user: mrrob@dbachecks1
  name: dbachecks1
currentcontext: dbachecks1
users:
- name: mrrob@dbachecks1
  authentication-type: basic
  basic-auth:
    username: mrrob
    password-encryption: none
    password: REDACTED
PS > 

obviously I cannot connect with this user.

So I create a new user

# so we can create a new user in the config file. We have to set the password as an environment variable

$env:SQLCMD_PASSWORD='dbatools.IO'
sqlcmd config add-user --name sqladmin --username sqladmin --password-encryption dpapi

sqlcmd config view

and then a new context with the new user

sqlcmd config add-context --help

sqlcmd config add-context --name dbachecks-sqladmin --endpoint dbachecks1 --user sqladmin

and show the funky off

# and now we will be able to log into the container and run a query

sqlcmd config use-context dbachecks-sqladmin
sqlcmd query "SELECT Name From sys.databases"

# and even open it in Azure Data Studio

sqlcmd open ads

at this point config looks like

PS > sqlcmd config view
version: v1
endpoints:
- asset:
    container:
      id: 891248e8ab58c3c339333b3f40a62c40b0f288c00ea60a46f9dbc9c288633775
      image: dbachecks/sqlinstance1:v2.36.0
  endpoint:
    address: 127.0.0.1
    port: 1433
  name: dbachecks1
contexts:
- context:
    endpoint: dbachecks1
    user: mrrob@dbachecks1
  name: dbachecks1
- context:
    endpoint: dbachecks1
    user: sqladmin
  name: dbachecks-sqladmin
currentcontext: dbachecks-sqladmin
users:
- name: mrrob@dbachecks1
  authentication-type: basic
  basic-auth:
    username: mrrob
    password-encryption: none
    password: REDACTED
- name: sqladmin
  authentication-type: basic
  basic-auth:
    username: sqladmin
    password-encryption: dpapi
    password: REDACTED
PS > 

all wonderful until we do

#cleanup 

PS > sqlcmd delete --force
Current context is "dbachecks-sqladmin". Do you want to continue? (Y/N)
y
Removing context dbachecks-sqladmin
Stopping dbachecks/sqlinstance1:v2.36.0
Current context is now dbachecks1
PS > 

Now config looks like this without any context but with a user

PS > sqlcmd config view
version: v1
endpoints: []
contexts:
- context:
    endpoint: dbachecks1
    user: mrrob@dbachecks1
  name: dbachecks1
currentcontext: dbachecks1
users:
- name: mrrob@dbachecks1
  authentication-type: basic
  basic-auth:
    username: mrrob
    password-encryption: none
    password: REDACTED
PS > 

if I try to delete my current context

PS > sqlcmd delete --force
Error: No endpoints to uninstall
PS >

I have to add an endpoint back again to be able to delete :-)

PS > sqlcmd config add-endpoint --name dbachecks1 --address localhost 
Endpoint 'dbachecks1' added (address: 'localhost', port: '1433')

HINT:
  1. Add a context for this endpoint: sqlcmd config add-context --endpoint dbachecks1
  2. View endpoint names:             sqlcmd config get-endpoints
  3. View endpoint details:           sqlcmd config get-endpoints dbachecks1
  4. View all endpoints details:      sqlcmd config get-endpoints --detailed
  5. Delete this endpoint:            sqlcmd config delete-endpoint dbachecks1

PS > sqlcmd delete --force
Operation completed successfully

[stuartpa]

Great to see sqlcmd being used with different container repos! Interesting use case here.

Is the fix here, to all the sqlcmd delete to succeed, even if the endpoint no longer exists. (or should we not delete the endpoint in the first place, because another context is referencing it!)?

Or should we enable being able to specify the user in the "sqlcmd create" statement, so we don't auto-generate one, and use the pre-existing user?

[SQLDBAWithABeard]

I like the idea of being able to specify the user (and pwd via env variable) in the sqlcmd create statement.

I think you should not delete the endpoint if it is being referenced by another context because that will allow me to also have context for 'admin roles' and context for 'app roles' against the same container. (I am trying to think of real world use cases here where people would create additional users)