-1

I want to make connection to SQL server configured on On prem using Powershell. But I don't want to pass any credential in connection string as shown below

#making a db connection string
$connString = "Data Source=$SqlServer;Database=$Database;User ID=$SqlAuthLogin;Password=$SqlAuthPw"
        
#Create a SQL connection object
$conn = New-Object System.Data.SqlClient.SqlConnection $connString
        
#Attempt to open the connection
$conn.Open()

Is there any other way from which i can pass credential.

Any help will be thankfull.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Shubh kumar
  • 39
  • 5
  • 1
    you can use windows authentication – Roshan Nuvvula Oct 10 '22 at 08:02
  • 1
    Which dbms are you using? (This isn't really related to the SQL _language_.) – jarlh Oct 10 '22 at 08:06
  • @Roshan, thanks for reply, for windows authentication also we have to pass credential as ConnectionString 'Server=$server;Database=$databaseName;UID=$DOMAIN\USER;PWD=$password;Integrated Security=true;' as given in following link https://stackoverflow.com/questions/38177582/powershell-script-to-connect-to-sql-db-with-windows-authentication – Shubh kumar Oct 10 '22 at 08:49
  • @jarlh, thanks for reply, i'm using SQL hosted on onprem – Shubh kumar Oct 10 '22 at 08:52
  • @Shubhkumar refer this https://www.sqlshack.com/connecting-powershell-to-sql-server-using-a-different-account/ – Roshan Nuvvula Oct 10 '22 at 08:54
  • @shubh kumar - Integrated Security = true, why do you have to specify username/password? from my point of view thats not the intention of it, it should automatically authenticate with the credentials of the caller by using kerberos/ntlm. – Toni Oct 10 '22 at 08:55
  • Based on your [earlier](https://stackoverflow.com/q/73931803/503046) question, I think you have got a [XY problem](https://xyproblem.info/). Thus, [edit] the question and explain with more details what you are trying to achieve and what constraints, if any, there are that prevent you from using, say, integrated security. – vonPryz Oct 10 '22 at 09:08
  • As already mentioned, no you don't pass any credentials when you use windows authentication. But then you have some housekeeping to be done to ensure it works, i.e. service accounts etc. – Nick.Mc Oct 10 '22 at 12:17
  • @Nick.McDermaid, Thanks for reply but I can't use windows authentication because most of users don't have access to database. So have to go with SQL username and Password , but not to pass password in connection string . Is it possible by any way. Should i have to use service account if so then what changes should i have to do in connection string. As I'm new to this a bit confuse. – Shubh kumar Nov 11 '22 at 09:27
  • @Roshan, Thanks for reply but I can't use windows authentication because most of users don't have access to database. So have to go with SQL username and Password , but not to pass password in connection string . Is it possible by any way – Shubh kumar Nov 11 '22 at 09:27
  • There is a known pattern for this but it's complicated. As already suggested, you need middleware in this case. This means your users connect to a "service" using their windows credentials, that service checks that they are allowed, then connects to the database using windows credentials, embedded in the service logon. Please do not ask the same question over and over again. – Nick.Mc Nov 11 '22 at 14:34

1 Answers1

0

You can use windows Authentication with Integrated Security.

# more secure windows authentication with current account

Invoke-Sqlcmd  -ConnectionString "Data Source=$SqlServer;Initial Catalog=$Database; Integrated Security=True;" -Query "$Query" | Format-Table
Roshan Nuvvula
  • 803
  • 1
  • 8
  • 28
  • Thanks for reply but I can't use windows authentication because most of users don't have access to database. So have to go with SQL username and Password , but not to pass password in connection string . Is it possible by any way – Shubh kumar Nov 11 '22 at 09:22