SQL Server 2008 :: VB Script / Connection Strings And Passwords
Nov 2, 2015
We run some VB scripts to extract data from our SQL server. Within these scripts we reference a 'read only' SQL userid and it's password in the connection string.
What I want to do is remove the references to the 'read only' user and it's password from the scripts.
Could we add the user that is running the script as a SQL authenticated user (that has read only access) thereby not have to reference any passwords in our script ?
View 3 Replies
ADVERTISEMENT
Mar 9, 2007
All --
Please help.
I have some questions about connection strings.
BACKGROUND...
Note that I am using the "SQL Native Client OLE DB Provider", SQL Sever 2005 Express, ASP.NET, C#.NET, and VS.NET 2005.
Note that I do not want to use the "Attach a database file" type of connection string.
Note that I am using the site http://www.ConnectionStrings.com as a reference.
Note that this "Standard security" connection string...<add name="LocalSqlServer" connectionString="Server=MYTESTOFFICESERVERS;Database=MyDatabase;Uid=MyDatabaseUser;Pwd=MyDatabasePassword;" />...does not work and causes the following run-time error...A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
Note that this "Trusted connection" connection string...<add name="LocalSqlServer" connectionString="Server=MYTESTOFFICESERVERS;Database=MyDatabase;Trusted_Connection=yes;" />...works but is less-than ideal because it uses a Trusted Connection rather than a Username/Password combination.
QUESTIONS...
(A) What exactly does the error in Item 4 mean?
(B) Do 4 and 5 imply that one must use a Trusted_Connection for all SQL Server 2005 Express connection strings when the database does not reside in the web application's App_Data folder or use a "Attach a database file" type of connection string?
(C) Are there any alternatives?
(D) What do you suggest?
Please advise.
Thank you.
-- Mark Kamoski
View 1 Replies
View Related
Dec 18, 2003
I should start by saying that I'm new to SQL Server and ASP.NET. My question is about connection strings. With so many possibilities of these strings, how will I ever know what is best to use or try when one does not work. Is there a rule of thumb or an article or even a book that someone can recommend? Something that will demystify this part of working with the SQL Server and ASP.NET?
Thank you very much for any help on this.
View 1 Replies
View Related
Dec 12, 2007
Hello,
I am wondering how it is going to work using SQL Server configuration type to store connection string in the SQL Server table. How does SSIS know what database to connect to if its connection string is store in the database? Thanks
View 5 Replies
View Related
Jul 27, 2006
We have an SSIS package that will be used for both our Test and Prod imports on the same server. The SSIS imports are identical expect that Test needs all connections pointing to the Test database while Prod need its connections pointing to the Prod database.
How can I change the connections, based on Test or Prod, used inside a single SSIS package? (I don't want to create two tweaked packages on the same server. If I find a bug in one of them, I have to correct it twice.)
TIA,
barkingdog
View 3 Replies
View Related
Jul 16, 2007
I created my SSIS package with connections to server databases. When I move the SSIS file to another machine it loses the password that is save in the connection. Is this a security feature? Is there a way to make it keep the passwords?
View 1 Replies
View Related
May 5, 2014
Occasionally I'm seeing the following error when tranlog or full backup maintenance plan is executing.
Failed-1073573396) Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection.
What is strange is that it goes away without any action from myself. We have a tranlog backup that is scheduled every hour. I had this error happen once in the middle of the night. It looks like the job was running fine because almost all the backups are there. I've searched and not found a resolution to this error. I checked the event log and don't see any issues and even tried rebuilding the maintenance plans.
View 7 Replies
View Related
Feb 19, 2015
I have a child package where the ConnectionString property of a Connection Manager is set by a Parent Package Variable Configuration. I set up a script task that brings up a message box with the value of the ConnectionString property right before the dataflow task.
MessageBox.Show(Dts.Connections["CPU_*"].ConnectionString.ToString());When I run the parent package, the message box shows that the connection string is changing with every iteration, but in the dataflow it always draws the data from the same source.
The connection manager is an ADO.Net type, RetainSameConnection is set to False, and I've been researching this for days.
(Update 2/23/2015): To make this stranger, when I look at the diagnostic logs, they tell me that when the new connections are being opened they are using the new connection strings.
View 2 Replies
View Related
Mar 12, 2007
I am having a strange problem in that when ever I create a connection to an OLE db source and use SQL Server authorization and save the password, the connection manager seems to "forget" the password. That is, when I click the 'save password' check box, and do a test connection, it connects fine. But as soon as I close that connection window, and reopen it, the password box is empty, and the 'save password' box is still checked. Even if I do a test connection at this point, it wont connect because it does not have the password.
Is this some sort of bug? Is there a workaround?
View 25 Replies
View Related
May 2, 2007
Hi Experts here,
Sorry if this query had been raised earlier. While In DTS Packages we retrieve connection details for all Connections in a Package from a table and then assign them to the following global Variables
gv_Source_User, gv_Source_Pwd, gv_Source_DataSource, gv_Source_InitialCatalog
Finally we reassign these variables to repective Connection Properties using Dynamic Properties Task. After Migrating to SSIS though we are able to assign almost all variables to Properties of Connection Managers via Expression except the Password which we donot find in the drop down list in order to assign gv_Source_Pwd.
Is there any work around to assign passwords dynamically?
Many Thanks
Subhash Subramanyam
View 4 Replies
View Related
Jul 11, 2007
Hello All,
I'm new to SQL 2005. I am setting up some SSIS packages which will connect to an Oracle database and copy some tables from it. These packages will then be scheduled to run on a daily basis. Because they will run automatically, it is required that passwords be saved along with the connection string. However, even though the password is saved (and encrypted, I checked the .dtsx in notepad), when I run the package, the connection to Oracle fails. Only if I respecify the password does it run correctly. How can I correctly save this password so that I can schedule automatic execution? Thanks for any info.
View 5 Replies
View Related
Jul 7, 2006
Users were able tolog on to our SQL 2000 servers with their passwords being case-insensitive.
Now with SQL 2005 some users can't logon because SQL 2005 passwords are case-sensitive.
Can SQL 2005 be configured so that passwords are NOT case sensitive???
Thanks!
View 6 Replies
View Related
May 8, 2008
I'm learning SSIS, and just started to use SSIS Package Configurations. I want to be able to switch between a dev and prod instance of our database.
I did not specify the password in the Configuration file (XML), figuring it would get it from the package itself. (That will be the same between dev and prod). But once I set up to use the Configuration, I started to get a "Login failed..." message. I added the password to the configuration, and it's the same. I quit using the configuration file at all, and then it works again.
This may be related, but when I show the properties on my connection, it shows stars for the password. When I go to the editor, the password field is blank, and if I test the connection, it fails. I type in the password, and then the connection works. I have the "Save my password" checked, but every time I go back, the password is blank. In fact, if I type in the password for the connection in the editor, and then go to the "All" page, the password is blank. It may be a red herring, but it sure looks like it's not really storing the password, and thus the Configuration file can't connect when it tries to get it. I can type in the password, test the connection (it works), close the editor dialog, open the editor dialog, test the connection again, and since the password is now blank, the connection fails.
What do I have to do to make that password actually stick!?
Without the Configuration file, when I save the package to SQLServer, it will run as a job, in spite of the password appearing to not stick. With the configuration file, it's not even running while still in Studio.
Here are at least some of the version information, which may or may not be useful.
Microsoft Visual Studio 2005
Version 8.0.50727.42
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: Professional
Microsoft SQL Server Analysis Services Designer
Version 9.00.3042.00
Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00
Microsoft SQL Server Reporting Services Designers
Version 9.00.3042.00
SQL Prompt 3.5
Thank you for any help you can provide.
-thursday's geek
View 12 Replies
View Related
Nov 11, 2006
I am trying to develop a web site. I have a local ms sql database on my machine.
I am trying to connect to a ms Sql database on a goDaddy server from the application.
I am trying to understand the connection string and its total properties.
here is what I think should be in my web.config file
<
add name="Personal" connectionString="Server=whsql-v12.prod.mesa1.secureserver.net;
Database=DB_XX10;
User ID=myID;
Password=myypassword;
Trusted_Connection=False" providerName="System.Data.SqlClient"
/>
<remove name="LocalSqlServer"/>
can someone please tell me where I am going wrong, Thanks for your help.....
View 7 Replies
View Related
Nov 24, 2007
I developed an application and deployed it onto a hosted provider. I use SQL 2005 Express for development SQL 2000 for production. Both systems work.
I backed up my production database and wish to use it for my development testing. I restored it as a SQL 2000 database on my development machine and changed my connection strings to point to it. I added it as a new Data Connection in Database Explorer and can see all of the data through it, but I see that all the tables now have the owner name in parenthesis as part of the table name.
My application now fails trying to find any of the tables. It works if I include the owner name as part of the SELECT statement. So much for data separation.
I don't know where to start. Is this an issue between SQL 2005 Express and SQL 2000, or is it an issue between SQL and ASP.NET, or is it a hosting provider issue, or what?????
View 6 Replies
View Related
Jan 24, 2008
Hey guys, don't know if this is in the right place but here goes.
I'd like some advice on optimizing the way I write my code. Here's the scenario. I have a select string for 3 different tables. One for the main gridview, and two others that are binded to dropdownlists.
So I created a Sqldatasource1,2,3 and had to create the connection strings for each one.
ex. SqlDataSource1.ConnectionString = "blah", SqlDataSource2.ConnectionString = "blah", SqlDataSource3.ConnectionString = "blah"the thing is, the connection string itself is the same. Is there a way to consolidate that? So that when I use a select/update/insert command for each sqldatasource i dont have to have another connection string? Also, having multiple connectionstrings, does that slow down the performance?
View 3 Replies
View Related
Mar 14, 2008
Hi,
Im struggling to work out the connection string to my ms sql database.
My website is being hosted with freezone.co.uk and I have a ms sql database called "aspnetdb". I usually log into the database with a username but no password. I don't no what other information is needed to create my connection string?
Thanks, Mark Lawton
View 2 Replies
View Related
Nov 6, 2006
I have created a SSIS package, and got it to perform as I want.
I am having a problem though with connecting to my data source (OLE DB Source) using SQL Server Authentication.
If I use Integrated security, I have no problems. If I use SQL Server Authentication, I get errors.
Here is the connection string that works:
Provider=SQLNCLI.1;Data Source=.SqlExpress;Integrated Security=SSPI;Initial Catalog=MyDataBase
Here is the connection string that fails:
Provider=SQLNCLI.1;Data Source=.SqlExpress;Persist Security Info=True;Password=**********;User ID=myUserid;Initial Catalog=MyDataBase
The strange thing is that when I use the Connection Manager dialog screen and click on "Test Connection", I get the response that the connection succeeded. It is only when I try to run the task that I get an error.
Here is the error I am getting:
[OLE DB Source [1]] Error: The AcquireConnection method call to the connection manager "MyDataBase" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "MyDataBase" (1) failed validation and returned error code 0xC020801C.
I have eliminated the obvious errors (incorrect Userid or password). Also, I am unable to use Integrated Security as this package needs to be distributed to various systems that may or may not be on our company's primary domain.
It seems I am missing something really basic here, but am not seeing it.
Any thoughts?
View 12 Replies
View Related
Dec 13, 2006
This is probably a very basic question and I apologise for asking. Can an app developed with VWD using SQLexpress be used with server2000? if so how do I modify the connection string.
View 2 Replies
View Related
Feb 5, 2007
Hi,
I am looking to allow a user to select which database they need to connect to, and then for them to be able to use that connection string until they choose a different database. I have a separate database specifically to hold the list of databases with the respective connection strings, and various forms that are currently looking at the web.config for the connection string. Unfortunately, I can't just put all the connection strings into the web.config as the number of databases available to the user will increase on a weekly basis which will be handled by a database administrator, so I want a user to select from a list of databases (for which they have permissions), and for that connection string to remain for that user. Potentially there will be 20 or more users each connected to a different database (all SQL Express).
If anyone can provide any help on this I will be truly thankful as I've hunted on the internet for hours and have been unable to find anything that has helped.
Thanks in advance.
Paul
View 4 Replies
View Related
Feb 6, 2007
Dynamic connection strings
I have found lots of people asking for something similar to this but cannot find any solutions - can you help?
I am developing an application (ASP.Net2) in which users are associated with 'Clients' and every Client has their own SQL Server database.
Users are authenticated using standard ASP.Net Authentication via a separate database (common for all users). This includes a table linking them to a Client and each Client record includes the connection string to their database. Currently a default connection string is held in the web.config file.
My problem is that I don't know how to dynamically change the connection string after a user has logged on. I have a large number of databound controls, many of which are declared and some are coded in the VB.Net code behind. I know I can use code behind to change the connection string for a SQLDatatSource:
I thought I had it cracked with the following:
Public objClientConnection As New ConnectionStringSettings
Public Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs)If Session("ClientDBConnectionString") <> "" ThenobjClientConnection.ConnectionString = Session("ClientDBConnectionString")objClientConnection.Name = "ClientDBConnection"objClientConnection.ProviderName = "System.Data.SqlClient"ElseobjClientConnection = ConfigurationManager.ConnectionStrings("WebTool1ConnectionString")End IfEnd Sub
The session variable Session("ClientDBConnectionString") holds the client specific connection string and is populated when the user logs on. If this is not populated then the connection string defaults to one collected from web.config. This should ensure there is always a design time connection string available.
The trouble is, when I try and declare the SQL data souce like this:
<asp:SqlDataSource ID="dsTest" runat="server" ConnectionString="<%# objClientConnection.ConnectionString %>" SelectCommand="usp_SEL_DocumentTypes" SelectCommandType="StoredProcedure" ></asp:SqlDataSource>
I get an error: The ConnectionString property has not been initialized.
I have been going around in circles on this for days and would really appreciate some help. Am I on the right lines? What needs to change to make it work? Is there a better way?
Many thanks,
Cliff
View 50 Replies
View Related
May 31, 2007
What am I doing wrong?
This works:
SQLConn.ConnectionString = "Data Source=McD_DB;Initial Catalog=McDFeelGood;User ID=uuu;Password=ppp"
Now I want to put it in my web.config, but these don't work ("ConnectionString property has not been initialized"):
SQLConn.ConnectionString = ConfigurationSettings.AppSettings("McDFeelGoodConnectionString") (VS IDE says this method is obsolete)SQLConn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("McDFeelGoodConnectionString")
<connectionStrings> <add name="McDFeelGoodConnectionString" connectionString="Data Source=McD_DB;Initial Catalog=McDFeelGood;Persist Security Info=True;User ID=uuu;Password=ppp" providerName="System.Data.SqlClient" /></connectionStrings>
View 1 Replies
View Related
Sep 19, 2007
Hello,
I'm trying to setup a typed dataset with a table adapter in .NET 2.0, and the problem I am having is that I cannot get the table adapter to use existing connection strings setup in the web.config file. How can I get it to do so? It doesn't see the connection strings, and so it wants me to create a new connection, which I don't want to do.
View 2 Replies
View Related
Aug 2, 2004
My question is, are the user id and password optional when creating a database connection string? I've created an asp page without including them, but in my aspx page (which is basically supposed to do the exact same thing as the asp page), I'm receiving the following error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Source Error:
Line 48: DisplayConnection = New SqlConnection("Server=infomart;Database=psg")
Line 49: DisplayCommand = New SqlCommand("SELECT RollOutID,RequestedDate,Background,Product,StagingPushDate,ProdPushDate FROM rolloutrequests ORDER BY RollOutID DESC", DisplayConnection)
Line 50: DisplayConnection.Open()
Line 51: DataGrid2.DataSource = DisplayCommand.ExecuteReader()
Line 52: DataGrid2.DataBind()
Source File: c:inetpubwwwroot
olloutrequests
olloutrequests.aspx.vb Line: 50
Stack Trace:
[SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +472
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
rolloutrequests.WebForm1.Page_Load(Object sender, EventArgs e) in c:inetpubwwwroot
olloutrequests
olloutrequests.aspx.vb:50
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
Any help would be greatly appreciated! Thanks!
View 1 Replies
View Related
Oct 1, 2004
My app is in a shared hosting environment. They use SQL Server authentication and I'm not wild about storing user ids and passwords in web.config. What's the best practice for storing this connection string information?
Thanks
View 3 Replies
View Related
Jan 17, 2007
I am trying to construct a connection string via iSeries OleDb provider. The default list of libraries on the AS400 for my user is fine for production. But in test scenarios the code/data libraries are rolled out through a sequence of test libraries. How do I specify the additional libraries for testing.
I connect fine and can execute sql. the statements like insert into TestLibrary.Table work fine, but the associated trigger code isn't called.
Would it be better to use the .Net Connection set of classes instead. I switched from ODBC to OleDb because of assumed thread safety issues.
View 3 Replies
View Related
Nov 20, 2007
Hello everyone! I am having some more problems while trying to get my site online. I built the site offline using Visual Web Developer Express Edition, and used "SQL Server Express" that comes with ASP.Net. Therefore, I have the ASPNETDB.mdf and mydatabase.mdf in the App_Data folder. Now, on my local machine everything runs fine. When I tried to put the site online however, I get the following error:An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be
caused by the fact that under the default settings SQL Server does not
allow remote connections. (provider: SQL Network Interfaces, error: 26
- Error Locating Server/Instance Specified) This is the connection string in my web.config file: <connectionStrings> <add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|mydatabase.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/> </connectionStrings> Now, my web-host does have SQL Server 2005. Does my ASP.Net application use that though, since I have it running just through SQL EXPRESS? Also, does anyone know where I could change my "SQL Server 2005" settings? My web-host uses Plesk 8.2.0, and the database managing program is "ASP.Net Enterprise Manager", but I can't find any options anywhere to change the Server settings to allow remote connections. Any help would be greatly appreciated! Thanks in advance, Markuu
View 4 Replies
View Related
Jan 3, 2005
Hey all,
I'm coming from ASP and I used to have a global connection string accessible to all of my ASP pages that I'd use for all of my data access. This was really convenient because I could easily switch to a backup or local data source for testing/debug by changing the connection string in one place.
Now, I'm using ASP.NET with Web Matrix and I love the drag and drop functionality but its dropping my connection string all over the place. How can I do this and keep my connection string in one spot so I can have the same convenience of switching data sources.
Thanks in advance!
Larry
View 3 Replies
View Related
Mar 18, 2006
From my Web.Config<connectionStrings> <add name="CustomerConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename="D:-- Work Documents --WorkezWebApp_DataASPNETDB.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>It's obvious to me that the next in orange would cause my db application not to work when i roll it out onto the net.Am i correct to say this? I recalled i needed something like "Server.mappath("path-name")"anyone know what i need to do?
View 1 Replies
View Related
Jul 11, 2002
Due to some problems, we have to reformat Our Windows NT4.0 Server.
The SQL Server 2000 has several ODBC Configured as
"User DSN" and "System DSN" .
Question: Is there any way that we could copy these User DSN and System DSN
Config Files so that after Rebuilding Server AND Database, we could Re-aaply/
Re-create these ODBC DSN specifically "System DSN" as they are More Number.
Thanks
View 1 Replies
View Related
Jul 11, 2002
Due to some problems, we have to reformat Our Windows NT4.0 Server.
The SQL Server 2000 has several ODBC Configured as
"User DSN" and "System DSN" .
Question: Is there any way that we could copy these User DSN and System DSN
Config Files so that after Rebuilding Server AND Database, we could Re-aaply/
Re-create these ODBC DSN specifically "System DSN" as they are More Number.
Thanks
View 1 Replies
View Related
Oct 16, 2006
I was reading through the following post regarding dynamic connection strings:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=812814&SiteID=17
In this posting they talk about setting up a configuration file to setup the connection string, but I am not sure how to apply that connection string to my connection manager. When I go into my configuration file, I can see the connection string, and when I go into the advanced properties of the connection manager, under the "Named ConnectionStringFileName" property, I set the property value to the name of my configuration file, but when I test the connection I get an error that the connection string property has not been initialized.
Can someone please assist me with identifying what I am doing incorrectly?
Thanks in advance for the assistance!
Craig
View 10 Replies
View Related
Nov 5, 2007
I've been planning on putting all my connection strings for my SSIS packages into package configurations. So far, I've been using XML config files, and these strip out the passwords in connection strings which then have to be retyped every time in notepad.
Has anyone got some better guidance regarding this? Could I use another type of package config to avoid this problem?
My contraints are such that I cannot use integrated security for the packages, so the password MUST be in the connection string. However, I can place the config files in a secure location where only the package and admins have access to them.
View 1 Replies
View Related
