-2

I am trying to identify how osql and sqlcmd resolve server name and user to connect to that server when we do not specify server using -S option and use -E option. Below is the output for various queries I ran.

  1. List all available servers on the network
    osql -L -- Returns all the servers on the network except the one that it connects to by default
    sqlcmd -L -- Returns the same list as above
  2. Connect to the default server without using -S option
    osql -E -d dbname -Q "select current_user" -- does not return the current user. Instead returns a user configured in the SQL server which I can see is present in windows registry but not sure how any why osql picks it.
    sqlcmd -E -d dbname -Q "select current_user" -- gives and error Named pipes provider: Could not open connection to SQL Server
  3. Connect using server name and -S option
    osql -E -S servername -d dbname -Q "select current_user" -- same output as in point 2 above
    sqlcmd -E -S servername -d dbname -Q "select current_user" -- gives the login id used to login to the system from where I am executing this query\
  4. Execute command remotely
    osql -E -d dbname -Q "select current_user" -- gives the same output as point 2
    sqlcmd -E -s servername -d dbname -Q "select current_user" -- throws an error Login failed for computername where computername is the actual windows computer name

I have been at it for days now trying to find how is osql working. I need to figure this out to move to sqlcmd.

Edit 1: I can confirm that there is no environment variable OSQLSERVER

Sachin Kumar
  • 808
  • 3
  • 11
  • 29
  • 4
    `ChatGPT could not help either.` did you expect otherwise? Have you actually checked the [-S parameter documentation](https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility?view=sql-server-ver16#-s-protocolserverinstance_nameport)? – Panagiotis Kanavos Jun 28 '23 at 08:32
  • Yup. Since I used it to solve few issues with some legacy code. Yes, I did check the documentation and it is how it works for sqlcmd. I am all good with sqlcmd but not with osql. – Sachin Kumar Jun 28 '23 at 08:34
  • Did you read the documentation? The answer is right there – Panagiotis Kanavos Jun 28 '23 at 08:35
  • Relevant [dcoumentation](https://learn.microsoft.com/en-us/sql/tools/osql-utility?view=sql-server-ver16) for OSQL too. What about this and the other documentation didn't you understand about the `-S` parameter? We can then *try* to elaborate. – Thom A Jun 28 '23 at 08:37
  • ChatGPT generates text based on your text. It needs *better* questions than the ones you'd ask another human, or post in a forum. Otherwise it just generates garbage that looks good enough if you don't know what the real answer should look like. If you're lucky, it will show you the actual documentation you'd have found yourself a lot sooner. If no, it will just generate nice looking fake stuff – Panagiotis Kanavos Jun 28 '23 at 08:37
  • Yeah. I know how to use ChatGPT. – Sachin Kumar Jun 28 '23 at 08:38
  • And what does the [osql documentation say?](https://learn.microsoft.com/en-us/sql/tools/osql-utility?view=sql-server-ver16) Did you read the `-S` section? – Panagiotis Kanavos Jun 28 '23 at 08:39
  • If you look at the different scenarios and the output that I got by using sqlcmd and osql you would understand that I referred the documentation first. – Sachin Kumar Jun 28 '23 at 08:41
  • Maybe you should just specify the names and usernames explicitely instead of trying to reverse-engineer old Microsoft products with a lot of hair and magic gathered through the millenia – siggemannen Jun 28 '23 at 08:48
  • @siggemannen I would if I had a non-prod environment where I could test that. Since it is production only I just want to make sure I have done all I could before going down that path. – Sachin Kumar Jun 28 '23 at 08:49
  • 2
    Being explicit certainly apepars to be the solution; if you want explicit expected behaviour then be explicit in that request. – Thom A Jun 28 '23 at 09:11
  • If you always want to use the default instance on the same machine you can do `-S .` – Charlieface Jun 28 '23 at 09:33

1 Answers1

0

After days of effort, I found that our organization had built an executable and named it osql. That was the reason that a new installation was not working but the old one was.

Sachin Kumar
  • 808
  • 3
  • 11
  • 29