I have NT SQL 7 and Web Server on same box. I have a new server with W2K and SQL 2000 installed (no IIS). I am migrating the databases over and when done I will install W2K on the old box and it will remain the IIS Web server front end to the new SQL 2000 Server.
On the old configuration, IIS and SQL 7 on the same box, I added the IUSER to the database from the local computer users list when needed for web access. How do I add that user now that the boxes are seperate? The acccout is not a local account on the new machine and the account is a loca account on the other machine so it does not authenticate to the domain so I can't pull it in from there.
We have an application that requires write settings to reportserver virtual directory for the IUSR account when anonymous is turned on during the install. Once the install is complete, we lock down the IUSR account so that it only has browse access to the virtual directory when enabling anonymous access.
We automate the uninstall and install of our daily builds and I'm trying to figure out if I can automated this process somehow either through command line utility or in vbs.
How to decrypt or encrypt without making user a db_owner. It is for a web application and I do not want make the web user a db_owner. Is there a way to make this work without making the user a db_owner. Currently the user is a db_datareader and db_datawriter.
I created an asymmetric key for encryption by password. I am not using a master key because I want to keep the password seperately on the web server, so a hacker cannot get access to both if database gets hacked.
These are the steps I took when I logged in to SQL server management studio using windows authentication:
CREATE ASYMMETRIC KEY ccnumber WITH ALGORITHM = RSA_512 ENCRYPTION BY PASSWORD = 'password';
INSERT INTO Payments (CreditCardNumber,enc_CreditCardNumber) values( '458724124', EncryptByAsymKey(AsymKey_ID('ccnumber'), '458724124') )
SELECT CONVERT(varchar(50), DecryptByAsymKey( AsymKey_Id('ccnumber'), enc_CreditCardNumber, N'password' )) AS Creditcardnumber , Creditcardnumber FROM payments where Creditcardnumber = '458724124'
When I use the above select statement it works if I make the user a db_owner but I get null if the user is just db_reader and db_writer.
Is there a way to do encryption without making the user a db_owner?
Currently we have one customer database containing various tables. As part of requirements for a new client, we need to manage their data in a totally separate database. The tables and structure are exactly the same but we would be loading data into a separate database.
I am looking for a way to combine tables with the same name in each database when I run queries, rather than having to query each database separately. Currently we actually have many queries set up in MS Access which use an ODBC link to query the data off SQL server. I am aware it is possible to apply a UNION SELECT in Access from 2 separate ODBC connections, but this is extremely slow.So my initial question is - is there a way to provide access to the tables from both databases over the same ODBC link? If this cannot be done over ODBC I guess we can consider more "modern" methods, but ideally we want to keep this in MS Access as that is where our existing queries are based. I was hoping that some kind of view can be treated as an ODBC connection.I mentioned ideally we want to keep the reporting queries in MS Access.
I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...
Hello, I have a developer here who wants to be able to access two databases on different servers with the same query statement. Both servers have SQL Server 7.0 ??? Any suggesstions?
we have some Access databases that are password protected. I get an error when I try an access these through a Linked Server. If I remove the password there is no problem.
I have tried adding the password to the security Information with a user of admin but this fails with a 7303 error. Should I not use admin as the user ??
Hello,I have an sql server 2000 on the network and one installed locally inmy computer. I use access as front-end.I go through odbc to connect to the sql server on the network.I was wondering if it is possible to setup the access file to link toeither server.I need to do this because I would like to use the local server as testenvironment. I know I could achieve that by creating 2 users on my XPclient and having odbc liking depending of the users.Is any other way to do that without login and logoff everytime I wantto use a different database.thanks,Giovanni
I know the SQL syntax to join two tables in different databases on the ame server, but does anyone know if a SQL statement will allow you to join tables from different databases on different servers?
I'm using MSSQL7, NT authentication and application roles so only my application can access the data. Also, other applications (like Excel) can not access the data and read it. So far, so good...
Yet, I noticed that if I try to access the SQL Server from another SQL Server on the network, it is allowed to see the list of tables, SP, etc. It is not allowed to open the table, but the Import/Export wizard is working and will allow retrieving data from the secured tables. If I change to MSSQL authentication, any user will be able to access the data from my application and I don't want that either.
Unless I'm missing something, this is a big problem, especially today where any VPN connection with valid user name and password can actually log in to the domain and therefore connect to the database via SQL Server.
By the way, the server still must allow access to users via applications so logins must exist. I just don't want other SQL servers on the network to be able to connect to and import/export, view table and SP, etc.
I'm using MSSQL7, NT authentication and application roles so only my application can access the data. Also, other applications (like Excel) can not access the data and read it. So far, so good...
Yet, I noticed that if I try to access the SQL Server from another SQL Server on the network, it is allowed to see the list of tables, SP, etc. It is not allowed to open the table, but the Import/Export wizard is working and will allow retrieving data from the secured tables. If I change to MSSQL authentication, any user will be able to access the data from my application and I don't want that either.
Unless I'm missing something, this is a big problem, especially today where any VPN connection with valid user name and password can actually log in to the domain and therefore connect to the database via SQL Server.
By the way, the server still must allow access to users via applications so logins must exist. I just don't want other SQL servers on the network to be able to connect to and import/export, view table and SP, etc.
Having a little problem with linking to Access tables. I have an Access database that has in it, some linked tables. After setting up the linked server in SQL server, none of the linked tables in Access show up, only the tables that were created or imported show up.
Is there anything you can do to get the linked tables in Access to show up under the tables list in the SQL linked server?
Being an IT consultant, I have to visit a number of customers having applications running on SQL Servers that implement trusted connection security model. In addition, all of these companies have their own DOMAIN settings, and security implementation.
How can I access to databases without changing the DOMAIN settings of MY notebook? I do not want them to switch to SQL security model, and I do not want to create any security hole in those companies.
What shall I want from the system administrator so that I can login into their domain without changing my domain settings?
I am facing a strange issue with accessing data from SQL server 2000. We are using SQL Server authentication for connecting to SQL Server 2000 and ADO .Net SQL client in the application. The problem here is, we have databases installed in one of the servers in some other office location that is in different state but on same domain. We do development from two locations that is one from Virtual Machines hosted on servers where Database Servers are located and on our local desktops in India.
Now when I developed a test client in Virtual Machines which will call a web service to get datasetof some data and show them in DataGrid. When I run the test client in debug mode from Visual Studio 2003, then I am getting data in the DataGrid. Even when I just run the exe from Virtual Machine still I am getting data.
But when I copy debug folder of the test client to the local desktop in India and when I run the Test client I am not getting any data loaded on to the DataGrid. I am getting an empty dataset when I am running from the local desktop.
Also I have converted the VS2003 project to VS2005 and when I run the application from Local Desktop then I am getting the data loaded fine. I could not understand what is the problem, what is the difference between accessing data from VS2003 and VS2005 using webservice.
Is this a firewall issue or I am missing any thing here. The webservice method I am calling to get data is actually returning dataset when I am calling this method directly from the browser also and even through application in virtual machine.
I am still not able to come to terms with this problem. This is severely affecting our deployment. Please help us resolve this problem. Please let me know if you need more information.
I'm looking to deploy some SQL Server reports and I want to restrict the access that the users have. Currently when connecting to the reports site they have access to a lot of functionality through the header bar, for example - Properties - New Folder - New Data Source - My Subscriptions - Site Settings - Search etc.
How can I disbale or hide all these options so that all the user sees is the list of reports?
We have 1 machine running SQL Server 2005 x64 (64 bit). The other machine is backing this SQL Server up. It is a 32-bit machine, so it requires (?) a 32-bit version of SQL Server 2005.
Would this back-up machine work correctly on a database previously managed by a 64-bit machine and vice versa.
I have a report that was designed using SQL Reporting Services that sits on a SQL reporting server. It's nothing too exciting, it is essentially a three page application with legal jumbo on pages 2 and 3 and applicant data in fields on page 1.
We use rectangles to force page breaks to page 2 and to page 3.
When running the report on the report server, it shows and prints fine.
When running the report from the QA website internally, it shows and prints just fine.
When running the report from the production website from a machine internally, it shows and prints just fine.
When running the report from outside of the company network, the report is jacked. It obliterates large chunks of text, crams text together, and creates blank pages.
I need help in determining where I even begin with trouble shooting this!
can anyone tell me if they know of a way to automate the update process from development servers to live server, with little interference from an administrator
I have a development team that are constantly updating their databases along with their ASP code, and want to publish changes an a weekly basis. They have asked me for a way to take their new structures, tables, procedures etc, and copy them to the live servers, but NOT to interfere with existing customer data.
Funny I know – and I hate the idea btw :(
Any references, contacts, 3rd party tool recommendations welcome,
I am in the middle of a major migraton project, moving from x86 SQL 2000 to IA64 SQL 2005. I have a business need to link to several legacy servers. I have a number of problems I am trying to solve.
1) Linking a Kerberos server to a non-Kerberos server. 2) Linking x64 or IA64 servers to x86 servers. 3) Linking SQL 2005 to SQL 2000.
Two of the errors I am encountering are: ------------------------------ TCP Provider: An existing connection was forcibly closed by the remote host. Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. OLE DB provider "SQLNCLI" for linked server "SCDC250DB" returned message "Communication link failure". (Microsoft SQL Server, Error: 10054) ------------------------------ And ------------------------------ The OLE DB provider "SQLNCLI" for the linked server "SCDC250DB" reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "SCDC250DB". OLE DB provider "SQLCLI" for linked server "SCDC250DB" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)
If someone has worked through these problems before, I would appreciate it if you could direct me to the relevant documentation to resolve these issues.
Thanks!
Brandon Forest
Database Administrator
Data & Web Services Team
Sutter Connect Information Technologyforesb@sutterhealth.org
I am new to SQL I have a problem that I can’t solve. I have a column with Surname and name (SMITH, James) in one table with data I just need to separate in two columns in one Surname in the other one Name I know how to unite two columns using substrings but not to separate in two columns.
I want a user to be able to search for names in a table of clientsusing a single parameter, but be eable to use "and" or "or" IN theparameter.Like this:@NameSearch nvarchar(100)ASDECLARE @PartA nvarchar(100), @PartB nvarchar(100)CASE When @nameSearch Like '%' + ' OR ' + '%'SELECT @PartA =(how do I get what's to the left of "OR" or "AND")SELECT @PartB (how do I get what's to the right of "OR" or "AND")SELECTc.UniqueID,c.LastNameFROMdbo.tblClients cWHEREc.LastName = @PartAORc.LastName = @PartBCASE ELSESELECTc.UniqueID,c.LastNameFROMdbo.tblClients cWHEREc.LastName = @NameSearch
I want to know if 64-bit SQL Server 2005 is a separate install. I have been provided with SQL Server 2005 enterprise CD. I tried browsing through the folder but could not find 64-bit install.
I am assuming the CD I have is 32-bit because the system configuration check report had this message "64-bit ASP.NET is registered. Required 32-bit ASP.NET to install Microsoft Reporting service 2005(32 bit)"
The windows server I am trying to install is Windows Server 2003 Standard x64 Edition. I uninstalled the all the client and SQL Server 2005 install. Since my windows server 2003 hardware requirements are compatible for 64-bit SQL server 2005, I want to go ahead and install 64-bit SQL server 2005.
I need to do a inner join on tables from two separate databases. I understand that you can do this by using this type of syntax: select a.col1, b.col2from db1.dbo.tab1 a, db2.dbo.tab2 bwhere a.col1 = b.col2however, how do I reference the two databases in the following code? Thanks, Tim
Function GetConnectionString() As StringDim ConnectionString As String = ConfigurationManager.ConnectionStrings("MainWeb").ConnectionString Return ConnectionString End Function Using conn As New SqlConnection(GetConnectionString()) conn.Open() Dim sql As String sql = "SELECT CaskInfo.CaskID, CoCInfo.CoCName, AmendmentInfo.AmendmentName FROM CaskInfo INNER JOIN CoCInfo ON CoCInfo.CoCID = CaskInfo.CoC INNER JOIN AmendmentInfo ON AmendmentInfo.AmendmentID = CaskInfo.Amendment WHERE "For i = 0 To UBound(words) If i > 0 Then sql = sql + " OR " sql = sql + "(CoCInfo.CoCName + ' ' + AmendmentInfo.AmendmentName) LIKE '%" + words(i) + "%'" Next ' lblResults.text = sql' Exit Sub Dim com As SqlCommand = New SqlCommand(sql, conn) Dim result As SqlDataReader = com.ExecuteReader() Dim SearchResults As StringWhile result.Read() SearchResults = SearchResults + result.GetInt32(0).ToString + " " + result.GetString(1) + " " + result.GetString(2) + "<br>" End While result.Close() lblResults.text = SearchResults conn.Close() End Using
I have a data grid with dropdownlist.the dropdownlist is populated with datas wth a sql statement with 2 combined datamy sql : SELECT NAME + CAST(ID as CHAR(10)) FROM TABLE1When i select a value from the dropdownlist, i need to separate the data, name and id into different columnshow do i do it?Is there a way to manipulate the sql to do such a thing?
Has anyone implemented SQL 7 on a cluster(NT 4 Enterprise). It has been running for about 1.5 years with no problems. However, I now wish to manage the transaction log on a separate disk to the data, to increase performance. I think I would need to create a new Disk resource on the cluster for the transaction log. But am not sure of the consequences during failover.
Has anyone done this, so that the disks containing the transaction log also failover. There are some articles at the microsoft site, but none deal with managing the transaction log separately on a Cluster.
I want to keep Win98 and my files intact on my C drive then install another hard drive and install MS Back Office (NT 4.0, SQL 7.0) on the new drive. When I want to switch operating systems, I want to restart in dos, go to the new drive and launch NT. I can't dual boot because I have FAT32 file system on my C drive. Has anybody tried this? Do it work?
I am new to T-SQL and triggers Any help will be appreciated
I am trying to change this code to insert firstname, surname (taken from employee table on db A) to firstname, surname on customer table of DB B but also create cust_id on customer table and DB B. currently I am getting all rows of customer.cust_id filled with the same data whenever a new data is inserted into (firstname,surmname of employee table)
Create trigger gen_cust_id ON employee for insert AS Update customer SET cust_id =( SELECT Replicate('0',(4-DATALENGTH(CONVERT(varchar(10),i.id)))) + Convert (varchar(10),i.id) + Substring(i.lastname,1,3) + Substring(i.firstname,1,1) from employee C INNER JOIN inserted i on i.id=c.id) from employee C INNER JOIN inserted i on i.id=c.id
