Would someone please tell my how to share the same NT Login on a Publisher and a Subscriber Server. For example, one of
the requirements for Replication is that the Publication Server and
Subscription Servers should share the same SQL Executive account. I tried
doing this in Control Panel, Services Applet, Startup, and This Account Box. The Subscribing Server is not allowing me to map the login of the Publishing Server. I also tried within NT User Manager for Domains. I am not able to figure this out. I am trying to get Replication to work within a Workgroup on two NT 4.0 Servers SP3, with SQL Server 6.5 SP5a.
Hello -- Please excuse my extreme newbieness -- trying to get up to speed fast on SQL Server Express for work. I've been able to set up the software, create a database, and access it with no problems on my local computer, but I need to make the database available to other computers in my workgroup. I followed instructions -- enabled TCP/IP and Named Pipes and turned on SQL Server Browser, and I was actually able to manipulate one system database (the "master" database) from another computer but not any other databases, not even the other system databases. I can connect to any of the databases with the SQLCMD commands, but if I try to do any SELECT commands (other than in "master") I get an error: "SELECT permission denied on object '[MyTable]', database '[MyDatabase]', schema 'dbo'". I was only able to get into "master" after adding "guest" as a user, but adding this user to the other databases has no effect. One interesting thing: When I used the sp_table_privileges command on each database, all but "master" showed all privileges being granted to and from "dbo", but "master" didn't show anything being granted to or from anybody. Does anyone know what could be going on here? Am I missing something obvious? Thanks for any and all help!
I'm developing a scientific appliation - to forecast the spread of disease. The algorithm has been designed such that different subpopulations reside on separate computers.
The model uses the same set of data repeatedly, and only returns a small signature that reflects the appropriateness of a particular set of parameters.
The model does however change, and I'm looking for a way to roll out this model (which only contains T-SQL, CLR assemblies and service broker code).
I have considered setting up a database on each computer which has the 'data' files which are static, and a separate database which contains the algorithm which can be amended.
Could one then detach the algorithm database making the new version that can then be rolled out (by attaching it to each computer used)?
Is this the best practice? Does anyone have a suggestion as to how to do this?
I have an application which works with SQL Server. When the application attempts to load a page it needs to run certain queries. When it attempts to run a particular query it fails but I catch the exception and then log it. The following is logged:
System.Data.Ole.Db.OleDbException: Login failed for user 'CWAMB01DWH01ASPENT'.
Now what I don't understand is that this is the work group that ASPNET is run under, and I am running other queries to the database via SQL Server authentication. Why am I getting a failed login for this workgroup? Do I need to create a new Login for the Server in SQL Server, and then create a new user for the database with the same username as the Workgroup name? If so, then how does the password work for the SQL Server as the workgroup (CWAMB01DWH01ASPENT) obviously doesn't have a password.
Thanks, and I hope I have explain my problem clear enough.
I am trying to set up replication (transactional with updates) from a 2-node Active/Passive SQL Server 2005 Cluster to a SQL Server 2005 server in a Workgroup. When configuring the Snapshot Agent, Log Reader Agent and Queue Reader Agent from the Publisher (Cluster) we are advised not to use the SQL Server Agent Service Account for security reasons. When I specify a Domain Account I get an "unknown security error" staring the snapshot agent. When I select the option to run under the service account all works well. Strange thing is when I specify the service account I still get the "unknown security error" message. Anyone got any ideas?
Now, I am getting closer and receiving the following message in the Distribution Task (repl_subcriber is the owner, AutoStart is the Frequency):
"37000[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed - User: Reason: Not Associated with a trusted SQL Server connection."
I have 'sqlexecutivent' and 'pswd' defined on the CA (Publisher) and VA (Subscriber) Servers. I have 'sqlexecutivent' defined in the NT Administrator group on both machines. I have trusted the NT Administrator group to SQL Server on both machines (xp_grantlogin 'administrator', 'admin'). I have trusted the NT 'sqlexecutivent' login to SQL Server on both machines (xp_grantlogin 'sqlexecutivent', 'admin'). I have logged-in to both NT machines using the 'sqlexecutivent' login and 'pswd'. I have started the MSSQLServer Service and SQLExecutive Service using 'sqlexecutivent' and 'pswd'. When I set up the Topology, Publication, and Subscription I was registerred in SEM as 'sa'. I do not know if this is needed, but in SQL Server I also created 'sqlexecutive' logins and 'sqlexecutive' database users on both machines.
Please tell me what I am missing. Do I need to do anything with the repl_subscriber and repl_publisher login ids? Do I need to configure a DSN? Do I need to check anything in the registry? Maybe I need to just start over again with a fresh start by uninstalling the Replication Topology, Publishing, etc. Maybe I should try Multi-Protocol.
Again, these are two NT 4.0, SP3 machines running SQL Server 6.5 SP5a. They are set up in the same Workgroup. I can connect to both machines in SEM and by Mapping a Network Drive. The default netlibrary is set to named pipes.
As part of my appreciation for your efforts, I will document this process and mail it to you once it is working.
I have been attempting to set up replication between two 6.5 servers in the same domain, and all I get is a login failed error on my replication task. Both servers use standard security and an admin login for the server and exec service, and I am using the default remote server setup as installed with replication. I'm sure it will be something simple, but I can't see it. Thanks in advance for any help you can offer.
I am trying to correctly configure a very small replication - one table - between two test machines. Publisher is Windows server 2000. Subscriber is Windows server 2003. Both machines run SQL Server 2005 Enterprise Edition, SP 2 has been applied. I have created an alias from Publisher to Subscriber. In SSMS, I am able to connect to both pub and sub databases (sub is Northwind, by the way).
I was receiving Error 18456, State 11; now I'm reverting back to state 5, which indicates an invalid userid.
HOW do I set appropriate userid for replication? I have established identical id and password on both machines - the only difference between the IDs is the machine name. However, the ID I wish to use is NOT the ID that I use to log onto the publisher computer. This userid is inserted because I'm using Windows Authentication, and I cannot find any means to override it. So I set up the same ID on the publisher, but it doesn't matter what ID I use. I only have TCP/IP turned on because these machines are on different networks, and neither machine is joined to a domain. The alias uses the IP address (but we know that works because I have connectivity under SSMS).
Where can I turn and how do I get this done? I've been struggling with this for 3 weeks, and I'm still not convinced I'm asking the right question.
I hope someone who knows how to fix the following problem could shed some light:
I have set up a two-way transactional replication between two SQL 7.0 servers. The updates and inserts on server A is successfully replicated to server B. But when I tried to test if changes on server B is replicated onto server A, I got a 'login failed for user 'sa'' when I modified a user table. It sounds strange because I already logged in using 'sa' and all SQL servers use the same 'sa' login. I wonder if it is a Microsoft bug. The same problem happened on a pair of SQL 2000 Enterprise servers.
Replication-Distribution: Task 'CAServer_Page_VAServer_InPage' failed. 3700[Microsoft][ODBC SQL Server Driver][SQL Server] Login Failed - User: Reason: Not associated with a trusted SQL Server connection.
I checked under the SQL Security Manager, View, SA Privilege and my sqlexecutivent login is set up as:
CAServersqlexecutivent sa
Thanks in advance for any tips you can give me with this. Both servers are set up in a workgroup. We are not using Domains.
Also, should my table already exist on the Subscribing Server?
I'm "trying" to set up Replication. The Publishing/Distribution server is in one domain, and the subscribing server is in another. Both domains are fully trusted.
The synchronization step builds the .tmp file, but the repl_subscriber Distribution task bites the dust with an error message, "28000[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed".
The setting on the distribution options dialog box is ODBC, SQL Server. I'm not using a special login/password. I've even tried putting a user name and password there, and it doesn't work. The ODBC connections test out fine on both servers. Any suggestions where I've gone wrong?
Hi, Im getting this error when attempting to retrieve data from an sql database.
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: Cannot open database requested in login 'projectAllocations'. Login fails. Login failed for user 'sa'.
Source Error:
Line 13: objConn = New SqlConnection( "Server=LAB303-066NETSDK; Database=projectAllocations; User ID=sa;Password=mypassword") Line 14: objCmd = New SqlCommand("SELECT * FROM project_descriptions", objConn) Line 15: objConn.Open() Line 16: objRdr = objCmd.ExecuteReader() Line 17: While objRdr.Read()
Been looking through the forums for a solution to this problem.I already tried granting access through statements such as:exec sp_grantloginaccess N1'machineNameASPNET'But they don't seem to work.. i vaguely remember seeing somewhere a DOS command line statement that grants access to the ASPNET_WP and that fixed my problem before on another computer.. but this is a new computer and i forgot to write down the command.Can anyone help explain and propose a solution to my problem. Many thanxs.
I have a quick question about SQL 2000 server data sharing.
I am currently using SQL 2000 Server Enterprise and I need to do synchronous data sharing with flat files. I have an ODBC connection to the files. I was able to easily share this data by using linking in Microsoft Access, however, for the life of me I cannot figure out how to do the same in SQL. We are in the process of converting legacy code. The old code will of course be communicating with the flat files and the new code will be talking to SQL server. So it is vital that the 2 access methods are both pulling the same data. Does anyone have any ideas???
hello sir i have sql sever 2000 installed on my PC which i would like to share on network. when i am trying to create ODBC connection from other pcs on network i am getting error -- specified sql server not found. pls help me gayatri
Can you use SQL Express remotely instead of locally.
I have been told that I might be able to use SQL Express as my network server for sharing files. Is this possible, or do I need a different version of SQL to do this?
I have been reading the VBE forums and from what I gathered was that SQL Express is only for a local table only, is this true?
If not, what is the work around, and how do I go to another pc to find out if it is sharable?
I would hate to get several months in my app design and learn that I was headed in the wrong direction.
I am using the MSDE to connect to my ASP.NET application. I get this error after clicking the login button of my login page. Anyone know why this would happen?
Thanks for any help,
Cannot open database requested in login 'DataSQL'. Login fails. Login failed for user 'serverASPNET'.
How can I share stored procedures so I can call them form different databases ?
Basically, all databases have identical table names and structure but containing different data and I don't want to replicate all stored procedures on every database. There are too many and will also be very unpractical to maintain the code.
I would like to know if it is possible to link two databases together (in my case ASPNETDB, and another mdf database) so that I can run queries on those shared tables. For example, I would like to use the uniqueidentifiers from the ASPNETDB tables in my own tables.
Thank you!
(I do use the latest version of Visual Web Developer).
I am writing a DB-driven application, using Visual Studio. I have a database that i want to be able to move from one computer to another, and still be able to access it. It's probably something simple, but I must be missing out something.
The problem is that if i choose windows authentication, then the DB cannot be used on another computer. I also had no luck when using SQL Authentication.
How can this be done? What form of authentication should be used and how, so that I can just copy my DB from one computer to another, then log into the DB using a username and password?
Hi:I'm trying to setup a MS Cluster but I don't know if it is feasible toconfigure it in the way I think.I have two machines with win2k server and MSSQL-2000 one of them iscurrently performing as the production database and the other one isthe backup. The secondary one is keeping updated via the "LogShipping" technic.We almost covered all the other possible failures of the othercomponents (ie: network, power, application servers, etc), the data ismaintaned in a raid which is ok but we want to cover also thepossibility of that failure too (yes, you can call us paranoids!! ).The thing is we want to create a cluster that do not share the data,but each machine of the cluster have thei own set of the same data.My intention is to configure the cluster to detect a failure of onemachine and initiate the failover to the remaing one using theappropiate scripts related to the promotion of a secondary serverkeeped updateusing "log shipping".Have anyone some experience with that kind of solution ??Thanks in advanceLeonardo
Hi guys,I've got two apps that are going to have pretty much the same users and i wanted them to have the same ASPNET.MDF user database. Is it possible to store the ASPNET.MDF in a different location and then have the two apps access it from there or alternatively have it in the App_Data folder of one of the apps and then have the second app access it from there?Thanx
Hi; I have an ASP.NET application with a SQL Express database.Here is the connection string used on the web application:"Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|admindata.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" Now, i create a Windows application which will run on the server to perform some scheduled tasks, for the Windows application I used this connection string:"Data Source=.SQLEXPRESS;AttachDbFilename=D:WebSitesmywebsite.comwwwApp_Dataadmindata.mdf;Integrated Security=True;User Instance=True"The problem is, when the web site is running and using the database, the windows application can't connect to the database and i don't know where i'm doing wrong, if it's the web application connection string or, if it's the windows application connection string. I hope someone had the same experience and can point me to a direction. Thank you;Emerson Brito
I'm looking for some advice on how to manage reports that use the same query in their datasets. I have multiple reports that use several datasets that are the same. If I need to make a change to one dataset, I need to remember to update the other datasets. Of course I don't always remember to do that!
Is there a way to create a dataset in a single location and then share it? I was thinking of using a View but I don't think it'll accept the parameters.
I've been cutting & pasting the entire query as I make change but I'm afraid it'll mess that up or forget to update a dataset.
I have read a number of threads in this forum concerning SSIS packages and Protection Level. I regret to say that I did not manage to find one that covers the problem I am experiencing.
I have saved a package that contains a SQL Server Authentication login and password. In Properties for the Package I have set
ProtectionLevel = EncryptSensitiveWithPassword
I then save my Package and use F5 to run it in Visual Studio 2005 Team Edition. It runs just fine.
I then walk over to a co-worker's desk, have him "Get Latest Version" in Source Control Explorer, and then have him double-click on the SSIS Package. It indicates that he must enter the password, which we do, and then it begins validation of the package. By changing to EncryptSensitiveWithPassword (instead of EncryptSensitiveWithUserKey) many of the earlier error messages went away.
We still get one fatal error, though, which prevents him from pressing F5 to run the package on his PC. The error message is one reported in some other threads in this forum:
Error loading PackageName(C2097).dtsx: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
Naturally we are running this on a computer different from the one on which the package was created/last-saved, and running with a different Windows user.
The package is a fairly simple one: it merely copies a few dozen tables from Access to SQL Server 2005 on a central server.
Might the error message be related to the Access connection? (He has the same rights to the Access database that I do, but those are based on Windows Authentication -- which makes me wonder if that might be the problem. The Access database is located on a central server, and the access rights are controlled through User Groups in the Windows File System.)
I would like for others to be able to run this and other SSIS packages I have developed.
Ultimately we would like to store completed SSIS packages in the File System (rather than in SQL Server 2005), and allow anyone in our team, who knows the password(s) for the packages, to run them without difficulty.
Ok, I understand it is possible, but I still can't quite get the mechanics of it to work.
I create a new BIDS project, and add a package to it, and add 3 connection managers, and set the server, instance, DB, etc. for SQL connections. I change the name of each connection manager so the server name is not there, but use something more like a generic name of what the database is. Fine. I right click in the Control Flow area and go to configurations. I enable configurations, and save to a common place on the C drive. Save everything, exit, VS, fine, and I export all connection managers properties.
Then I start Visual Studio again. Create another new BIDS project, add a package to it, and add 3 connection managers. I don't actually connect them, but use those same generic names from the first iteration. I enable configurations for the package. I am somewhat expecting to see the connections change from the same-o-default to the data I used in the first go around.
We have a couple of databases set up, and we replicate data from certain tables between the two database. One of the tables we replicate is the Users table, in which we'd like to encrypt user passwords. Initially I created a certificate on both servers, and found that I could not DecryptByCert a password that was encrypted on the other server, and vice versa. It looks like all I was forgetting to do with supply a 'ENCRYPTION BY PASSWORD = ' parameter to CREATE CERTIFICATE. So, now I have the following:
CREATE CERTIFICATE Cert_UserPassword ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y' WITH SUBJECT ='TestingCertificate'
I ran that query on both of our servers, and I find I am able to decrypt the password on both servers. So, as far as I can tell, this is exactly the way I want it to work.
So, now for the question: Is this the right way to go about it? In order to decrypt the password on either server, it means I need to pass the 'pGFD4bb925DGvbd2439587y' password to the DecryptByCert command, which doesn't seem very secure. But if I don't use the 'ENCRYPTION BY PASSWORD', then the cert will be signed by the Master key, which is different on both servers, which will result in a certificate that can't decrypt what was encrypted on the other server.
Is there a way to take the actual certificate on one server, and export it to the other server, so that they're both using the exact same certificate to encrypt and decrypt? I would like to not have the password included in the Decryption command, if I can help it.
We receive data files from different external customers, and these files have identical layouts.
I'm planning to set up a package for each customer. Each package will contain a flat file source -> OLEDB transformation dataflow, (followed by other customer-specific data flows).
What I'd like to do is just create this dataflow once, parameterising the flat file and table names. Is it possible to include this dataflow in each customer package so that if the flat file layout changes, I can just modify the connection managers in the one place, and then recompile each package to pick up the changes?
I'm using an XML file to configure my dataBase connection string. I try to deploy my package on a new server and it works perfectly.
Then I made a second package which also need a configuration for the dataBase connection string. (I made the connection with the connection manager inside packages). The configuration is the same that for my first package, so I thought to use the same configuration file.
I can use the same configuration file but the problem is when I try to generate a deployment for my solution. I got an error which tell me that the xxx.dtsConfig file already exist and can not be copy again.
When I made the configuration in the second package I said that I want to reuse the file ... and I thought that for the deployment SSIS would know that it has to include that file only once ...
The scenario is an ETL that takes flat file feeds via FTP to move data into varous production SQL server databases nightly.
There are a number of packages involved, and this depends upon the type of data being sent.
There are a set number of servers and databases to receive the transformed data. I would like to be able to define say 3 servers, and maybe a couple of databases in each one time in the configuration. For simplicity lets say 6 databases total. I would like a single point of maintenance for these 6 locations. I would like all connection managers in all packages in all solutions to share these 6 settings in all connection managers. Is this possible? From my initial attempts, it would appear each package gets its own independent list of connection managers and which must be configured separately. I don't see how to share settings, which is really where the power of SSIS configurations would be.
Similarly, I would like to be able to locate flat files at given paths. The package may know the name of the file it is looking for, or the file it will create, and the folder path needs to be computed from a configured folder root, and for the package connection manager to store only the name of the file, which never changes.
I can not see how to set something like this up with connection managers and configurations. Perhaps I still need a highly customized solution to achieve this, as we did for DTS, where we would only execute packages using our own executor, which would load the package, search out all environment specific settings and modify them on the fly prior to executing the package.
Thanks for any direction you can give here. The books I've read seem to imply I might be able to do what I need here, but I can't seem to find the mechanics of making it happen.
I am in the process of designing a web application for our application that will be available to the general public.Basically will be used to collect information for a case report. I need to know if there is a way from our internal database driven application to push certain data up to the website that the database may be behind a firewall so I cannot make a direct connection to the external database. I thought I heard about some sort of data sharing protocol. I then will be storing the users entered data in the website database and pulling it from the internal application. So basically I need a secure way of sending and receiving data between a server/client app on a LAN system to the Web site database that could be hosted outside the LAN. I hope this made sense.
