I have an SSIS package that connects MSSQL2008 server to MSSQL2000 and pulls data off the 2000 server and transfers it to the 2008 server. The 2000 server was recently virtualized and since then the SSIS package will not authenticate. I can see both servers with OSQL -L/sqlcmd -L. All services, agents and browsers are running. The SQL user that runs the SSIS package can connect via Test Connection button in the Package Explorer>Connection Manager, but when I attempt to execute the package the error output indicates that the login failed, the password does not match. Error 18456 Sev 14 State 8. I am sure the password is correct as I just created the new SQL user to test. Am I missing something that the SSIS package may authenticate somewhere else? Should the connection string include the password, or does that get pulled form elsewhere in the SSIS package? I am not a DB admin/SQL guy so please excuse my green-ness if I am overlooking something simple here, which I hope I am. Thanks for any input

I have an onpremise SQL database server, to which, many applications connect to. Created CNAME record for the server and instructed users to change it in their app connection string too. Most of them will, but there will be some who don't, and will pose an issue. Therefor I wish to monitor the network if there is any app that's still try to connect to the SQL server via hostname, and not with the CNAME record. What is the best tool to monitor such traffic? -SQL profiler and extended event seems to have no knowledge about that information. -Wireshark can resolve IPs to DNS names, but not sure how it will know what connection string did the application use. -Monitor DNS queries on DNS servers maybe the most promising. All in all. What are the best tools to capture this information?

There are a lot of useful, similar questions on this topic, as well as this part of the documentation (https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-listener-overview?view=sql-server-ver15#SelectListenerPort) : >You can also designate a non-standard availability group listener port. However, you also need to explicitly use the target port in your application connection string when connecting to a listener. You also need to open permission on the firewall for this port. > >You can connect to the listener using the name and port (if not 1433). **The port can be either the listener port or the underlying SQL Server port that it's configured to listen on.** In this documentation there were practical examples of connection strings if you have 2 instances with port number 50254 and listener has port number 50123. It is shown that you can use either port number in your connection string. So my question is if you use a default 1433 port for SQL instances and lets say some random port 50123, can you utilize the AG listener functionality which allows to login to the AG without specifying port - because the connection string will allow blank port number, since the instances are running on the default port? And is this considered an OK thing to do and are there any drawbacks of using this kind of approach?. Edit: And one more thing, instance I am running Availability group from is a named instance. And when I connect to the listener via listener\NamedInstanceName it doesn't need port number to successfully connect even though the port of both instances and listener is a non default port 50123. Is this a possible *safe* way to bypass the need of port in the connection string as well?

In SSMS v19 I can successfully connect/login to a SQL server that is listening on the default port 1433 Server : server.env.domain.com,1433 Authentication : SQL Auth And I can query any of the databases hosted on this server. Does SSMS reveal the connection-string it is using to make this connection ? I've searched quite a bit and can't find it. Or is there an API I can call that would return the exact connection string used by SSMS ? I ask because I am unable to connect via another app located on the same client machine as SSMS. I would like to know exactly how SSMS is making its connection.

The MultiSubnetFailover option in database connection strings controls how the client attempts to connect to a database server, especially when multiple IPs are available. There is conflicting documentation as to whether the correct connection string syntax is: MultiSubnetFailover=Yes https://learn.microsoft.com/en-us/sql/connect/oledb/features/oledb-driver-for-sql-server-support-for-high-availability-disaster-recovery?view=sql-server-ver16 Or: MultiSubnetFailover=True https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sqlclient-support-for-high-availability-disaster-recovery Are both "Yes" and "True" supported? Or does it vary by driver? If the latter, is there a list of which drivers support which syntax? Update 2023-03-30: This Microsoft documentation implies that "True" or "Yes" will work for another option: > The default setting for the Persist Security Info keyword is false. > Setting it to **true or yes** allows security-sensitive information, > including the user ID and password, to be obtained from the connection > after the connection has been opened. Keep Persist Security Info set > to false to ensure that an untrusted source does not have access to > sensitive connection string information. https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/connection-string-syntax (emphasis added)

I'm trying to use SQL Server **LocalDb** to store the database by connection string. During first connection to the database I have an error about connecting to the database. In SSMS it is opening. To fix it I need to add **AttachDbFilename** with full path to the **.mdf** file in connection string, despite that I already have **Initial Catalog** property. After first connection I can remove **AttachDbFilename** and then connecting will work also perfectly fine. Reproduces only on the Windows Server OS. On Windows 10 everything works fine. What can be the fix of this issue without adding **AttachDbFilename** during first connection?

## problem i am trying to connect to the server using Microsoft ODBC Driver 17 for SQL Server through sqlcmd setting the -S (server) option pointing at 127.0.0.1,1434. i have confirmed this is the correct port. i cannot use the traditional ., (local), localhost options. **it must address the server by its loopback interface IP 127.0.0.1** (see end of post for additional context of why). > **TLDR: why can i only connect over the local IP 127.0.0.1 when the login has sysadmin role?** ## what i have tried when i connect using a login (local) that has the sysadmin role it works:

sqlcmd -S 127.0.0.1,1434 -U local -P **** -d ****
1>

however i am trying to connect through a new login i created that has more restricted access . therefore i do not want to give this new login the sysadmin role and instead it is using the default public role. when i try to connect the same way with this login i get Login failed for user 'TestLogin'

sqlcmd -S 127.0.0.1,1434 -U TestLogin -P **** -d ****;
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'TestLogin '..

i am trying to understand why sysadmin role somehow allows "remote" (all local on the host machine) access but without this role it fails. ## root cause the root cause is that sysadmin role is required to connect over 127.0.0.1. i cannot find any information why this is the case. ## Full Context my end goal is to be able to connect to the server from a containerized process using SQLALchemy/PyODBC. in this configuration i use host.docker.internal to access the host localhost (127.0.0.1). **i am able to establish the connection using the local (with sysadmin role) login from inside the container without issue.** however, when using the more restricted login it fails. so i tried to isolate the issue by attempting from outside the container and just calling sqlcmd directly from powershell on the host machine. as you can see from the original question i am having the same issue so it is not related to being in the container. i provide this extra context because from the container i cannot use the traditional ., (local), localhost options. **it must address the server by its loopback interface IP 127.0.0.1 since this is proxied to the container through host.docker.internal**

I've got a primary and secondary database that i've just configured a Failover group. My question is: Can existing connection still connect directly to the DB, or do i need to change all connection strings to my application? or is it just a "declarative abstraction" architecture on top of the active geo-replication currently used? Many thanks Current architecture Failover architecture

I need to 'swap' an old database server (Windows Server 2008, SQL Server 2005) with a newer/better one (Windows Server 2012, SQL Server 2008) I tested this swap yesterday and everything worked on the new server (PHP pages, ASP.net website) *except for* vbscript scripts, and a product developed in MS Access. The reason: The connection strings require the port to be included (On the old server they did not) I can accept this if there is no way around it, but it will mean changing a lot of scripts, and being forced to involve the third party who developed the MS Access product (It would be a breach of contract to modify the product ourselves) so I am hoping to avoid that. The server is using the default port, and is not set to use dynamic ports. The server used to be part of a cluster but it is no longer. The server has two instances (one dormant, services disabled, and one active) I have tried as best as I can to match the settings in sql configuration manager between the old server and the new server. I have tried creating an alias. I have tried with sql browser on, and with sql browser off. I have tried restarting services. I may have tried other things that I'm forgetting (this was yesterday) The only thing I haven't tried is removing the dormant instance. The reason for this is it looks like to do that you need to go through the motions of uninstalling sql server entirely, and so far I am uncomfortable trusting a googled source suggesting this in case It removes both instances or sql server entirely. So is there a way to make it so that I can connect to this server without requiring the port (and generally without having to modify connection strings at all)? ConString = "Provider=SQLOLEDB;Data Source=[server ip];Initial Catalog=somedb;" & _ "Network=DBMSSOCN;User Id=****;Password=*****" set conn = CreateObject("ADODB.Connection") Conn.connectiontimeout=500 Conn.commandtimeout=900 conn.open ConString

I've been hunting for this information for years now but I don't seem to be able to find it: Is there any documentation available on [learn.microsoft.com](https://learn.microsoft.com) providing a table with all valid connection string parameters, their valid argument values, their default values and their implications, for SQL Server and LocalDB? All I find are some tutorials and example pages, but no thorough reference page.

I have an SSIS package between a SQL2000 and SQL20008 server that cannot connect since migrating to VM. The SSIS package runs a data transfer between the two servers using 3 Connection manager strings. I am trying to understand what the values of the connection strings mean. I am not a SQL DB admin/tech at all. The 3 strings are below. Some of the values seem self-explanatory, others I do not understand. Data Source=1.1.1.1; User ID=SQLadm; Initial Catalog=SQL_db; Provider=SQLNCLI10.1; Persist Security Info=True; OLE DB Services=-13; Auto Translate=False; Application Name=SSIS-TransferPkg-{8ABA18EE-637E-424F-A3F7-F7E4EA50DD9D}1.1.1.1.SQL_db.SQLadm; Data Source=localhost; Initial Catalog=Server2008; Provider=SQLNCLI10.1; Integrated Security=SSPI; Auto Translate=False; Application Name=SSIS-TransferPkg-{E92CF7CB-23A5-4713-8593-F2FF92C980EA}localhost.Server2008; Data Source=localhost; Initial Catalog=Server2008; Provider=SQLNCLI10.1; Integrated Security=SSPI; Connect Timeout=10; Auto Translate=False; Application Name=SSIS-TransferPkg-{E28D4B65-CE56-4BD7-BEC4-02EE1738F568}localhost.Server2008.SQLadm; Specifically what do the following mean: Provider= OLE DB Services= yes, object linking services and I read the MS Doc here( https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms717922(v=vs.85)#:~:text=OLE%20DB%20provides%20common%20services,performance%20of%20OLE%20DB%20providers.&text=(OLE%20DB%20services%20cannot%20be,see%20Creating%20Data%20Source%20Objects.) but hoping someone can dumb it down for me. Also what do the 32 digit codes in {} mean? Is it a hash check/authentication or ID for some process? And for Application Name= this seems redundant. I am already running the transfer package, why does the name and the connection parameters (IP.DB.USER) need to be repeated at the end? Thank you for any input or resources I can reference! I am not a DB/SQL admin in any fashion.

I have a SQL server that pulls data from another SQL server via SSIS. We recently converted the server targeted by SSIS to VM. Since the VM conversion I have been getting "login failed for user '(username)'.". errors and the SSIS package fails to run. The connection string of the SSIS package indicates the username (which is correct for the target DB), but I do not see how it passes the password to connect to that DB and pull that data. So how does SSIS send passwords if not in the connection string? The Package Protection Level is set to EncryptAllWithPassword Windows Authorization only option to run.

I am running an SSIS package that transfers data from one (SQL2008) server to another (SQL2000). However after P2V conversion the SQL2008 server cannot execute an SSIS package due to a user authentication error. Lets say the package is called "Transfer-Go". In the connection string of that package can it call another SSIS package? In the SSIS library there is another package with the same that appears in the string (called Transfer-Now) name The string is below: Data Source=;User ID=;Initial Catalog=;Provider=SQLNCLI10.1;Persist Security Info=True;OLE DB Services=-13;Auto Translate=False;Application Name=SSIS--{8ABA18EE-637E-424F-A3F7-F7E4EA50DD9D}; So is this SSIS package connection string calling that package? And if the credentials are wrong in that package could that be why I am unable to authenticate? Thanks for any input, not a DB/SQL guy at all so I apologize if I sound green here.

I'm building a Sharepoint application with Nintex workflow that runs a single SQL query over multiple databases (on the same MS SQL server). Does it matter which database I specify in the connection string in terms of speed? So if my query looks like this:

col1, col2 FROM db1.table UNION ALL

col1, col2 FROM db2.table UNION ALL

col1, col2 FROM db3.table

would it make any difference if my connection string looks like this:

=***;Database=db1; Integrated Security=SSPI; Connection Timeout=900

or this

=***;Database=db2; Integrated Security=SSPI; Connection Timeout=900

or this?

=***;Database=db3; Integrated Security=SSPI; Connection Timeout=900

The table from db1 has more records than db2, which has more records than db3. EDIT: My query is actually more complex than what I wrote above, I just simplified it because I didn't know that would matter. The real queries have a

clause and a

JOIN

on a fourth database (db4). The compatibility level of db2 and db3 are

Server 2008 (100)

, for db1 and db4 it's

Server 2017 (140)

.

I'm writing some test code that is required to connect to a local SQL Server instance (SQL Server 2017 - Developer Edition) by IP address. I have the SQL Server instance up and running on my machine, but can't figure out the correct IP + Port to use for my connection string's Server property. As a starting point, I'm trying to at least manually connect to my server instance with SQL Server Management Studio (via the IP address) but I can't seem to figure it out. 1. How do I find the IP Address and Port of my local SQL Server instance? 2. What is the correct format I should be inputting the IP Address and Port into the Server Name field of SSMS (and is it the same format as a connection string Server property)? I've tried locating the IP address executing the following SQL code on it: SELECT CONNECTIONPROPERTY('net_transport') AS net_transport, CONNECTIONPROPERTY('protocol_type') AS protocol_type, CONNECTIONPROPERTY('auth_scheme') AS auth_scheme, CONNECTIONPROPERTY('local_net_address') AS local_net_address, CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port, CONNECTIONPROPERTY('client_net_address') AS client_net_address But the local_net_address and local_tcp_port fields are NULL and the client_net_address is set to ``: I've also tried going into the SQL Server Configuration Manager, under the SQL Server Network Configuration section, and enabling TCP/IP. Then I tried using a mix of the IP4 and IP6 addresses with Port 1433 (which is blank by default in the TCP/IP Properties, but I've tried leaving it blank and setting it to 1433) and that didn't work. (I made sure to restart the SQL Server service and SQL Server Browser service every time I made changes in here.) I've received the following errors when trying to connect via SSMS for the different combinations of the IP4 address with and without Port specified: **No Port Specified:** **Port Specified w/ Colon:** **Port Specified w/ Comma:** I've also tried connecting via 127.0.0.1 with the same combinations of Port 1433 above to no avail. (I receive similar errors except for the following combinations.) **127.0.0.1 w/o Port:** **127.0.0.1 w/ Port via comma:**

In general SQL Server Connection Protocols allows three different connection protocols: > 1. TCP/IP > 2. Named Pipes > 3. Shared Memory in linux by default installation we can connect to the SQL Server using TCP/IP, the question is **is there any options for named pipes or shared memory or UNIX sockets?** Also, it seems there is no option to enable or disable such features in Linux. TCP/IP: sqlcmd –S tcp:ServerName –U sa –P Named Pipes: sqlcmd –S np:\\ServerName\pipe\sql\query –U sa –P Shared Memory: sqlcmd –S lpc:ServerName –U sa –P

We are using Microsoft Azure App Services to host some of our Web Apps. Web Apps connect to on-premises SQL Server over hybrid connections. When new Web App needs access to the on-prem SQL Server, a dedicated SQL login is created, and the connection string (with password, etc.) is sent to the developer responsible for that app. Developer then goes to Web App -> Configuration and pastes the connection string into corresponding section, so the application could use it. The problem with this approach is that some other developers have Contributor access in a subscription, and they can see those connection string, and subsequently they can see the SQL Server login and password. Those SQL accounts can have elevated permissions on tables, which are normally not supposed for Developers to have, but as we can see SQL Serverpasswords are exposed, so these can be abused. Questions: 1. Is there a way to put connection string "somewhere else" - not in Configuration pane of the Web App, but in some other location where application can use it, but developer (even if dev is a Contributor) can not? 2. Is there a way for an Azure Web App to use Integrated Security=TRUE and login to SQL Server using some domain account, so developers don't see its password in connection strings? 3. Any other options? I know it is also policy thing, but right now it is what it is, and I can't say Developers are doing some abuse, no, but still would like to implement some secure solution.