I've been reading about the protocols and i've been trying to connect to my SQL SERVER using each one of the protocols, but i think I'm missing something, i dont have any problems using Shared Memory and TCP protocols, but I'm not pretty sure how to connect to my remote SQL SERVER using NAMED PIPES protocols. The books On Line of Microsoft refer that you can connect using this protocol using the next syntax: Connecting to a default instance by specifying a named pipe name:
APPHOSTpipeunitapp
Connecting to a named instance by specifying a named pipe name:
APPHOSTpipeMSSQL$SQLEXPRESSSQLquery
Connecting to default instance by name, forcing a named pipes connection:
np:APPHOST
Connecting to named instance by name, forcing a named pipes connection:
np:APPHOSTSQLEXPRESS
But I can't connect to my remote SQL SERVER, if i try the same with my local SQL SERVER i don't have any problems. I've been reading the articles so many times searching something about Named Pipes is only for local connections, but Microsoft say that it's posible to connect to a remote SQL SERVER, but i don't know how to do that. The firewall is disabled, I changed the APPHOST in the above syntax for the IP address of my SQL SERVER but it didn't work..
I have system database and user database file are present in G,H and W drive.The process is going to be - copy data from G to S, H to T, W to U. Rename G to X, H to Y and W to Z. Rename S to G, T to H and U to W. Reboot the servers. The original G, H and W will then be X, Y and Z. The old S will be the new G, old T will be H and old U will be W. My question is that after doing this whether my SQL server will start or not
I recently installed standalone version of SQL 2014 Standard on my work computer. I used Access before but I want to use a SQL server instead.
We have a shared drive that a file gets deposited every day at midnight. I want to be able to get this file and import it to the server (its basically a list of names).
Here what I have done so far:
I created the database
Created the file and successfully imported data into it using the Import Data feature.
I saved the SSIS package
Scheduled an Agent Job for this package to run at certain time,daily
At first the jobs would fail with a Access is Denied. I added a user under Credentials with my network account ( have admin rights on the work computer).Also added a Proxy for the Credential user I made.
Jobs fail with a “Cannot open data file” error. I tried changing things here and there, but I can’t get it to work.
Is it possible to configure transnational replication between two different domains also non trusted domains.
It's possible means what i need to take care before configure replication and how to configure transnational replication between two different domains.
I have been trying to use openrowset with a shared drive, and even though the share has "full control" permissions granted to "everyone" and the accout that SQL runs under has been granted explicit full control permissions I am unable to open the file which itself has no security on it.
Can I not use a \ path and only use mapped drives?
Thanks
below works...
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:5People.xls', [Sheet1$])
below doesn't work...
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\cluster02FileManager5People.xls', [Sheet1$])
I have a rather odd problem that hopefully you'll be able to shed some light on.
We want to back up the databases to a hard drive held on another server so I mapped the drive in explorer to the drive then went into Enterprise manager and tried to create a backup device and it won't see the mapped drive.
I've tried mapping to my PC and I can see that via enterprise managers backup stuff (infact any PC in the office works) but it won't see any of the servers even though we can map to them and access them via windows explorer.
I've tried when logged on via sa and the windows NT administrator and still no luck. In fact no matter what I type or do it fails and keeps telling me device error or device off line which it isn't.
On our test instance of SQL Sever we can backup to other servers but not the new live version!
Any thoughts on what might cause this to happen and how to fix it?
I have a rather odd problem that hopefully you'll be able to shed some light on.
We want to back up the databases to a hard drive held on another server so I mapped the drive in explorer to the drive then went into Enterprise manager and tried to create a backup device and it won't see the mapped drive.
I've tried mapping to my PC and I can see that via enterprise managers backup stuff (infact any PC in the office works) but it won't see any of the servers even though we can map to them and access them via windows explorer.
I've tried when logged on via sa and the windows NT administrator and still no luck. In fact no matter what I type or do it fails and keeps telling me device error or device off line which it isn't.
On our test instance of SQL Sever we can backup to other servers but not the new live version!
Any thoughts on what might cause this to happen and how to fix it?
We have applications connected to SQL using windows authentication. While having connection with Application user can also access to Database instance on the same time as well. We need to limit the access of user outside application.
I have SQL Server 2014 (Enterprise) on Server A. The service runs under DomainAAdmin.
The Client machine is B, User credentials DomainBUser.
DomainBUser has a share on B that contains a BCP data file. DomainAAdmin has full access to this file.
If I log onto A (the server machine) with either DomainAAdmin or DomainBUser credentials, and run SQL Server Management Studio with Windows Authentication I can run BCP sucessfully using the following:
BULK INSERT [MyTable] from 'Bsharedatafle.tsv' WITH ( KEEPNULLS , KEEPIDENTITY ) However, if I log onto B, (the client machine and the machine hosting the share) and try to run the same bcp command, I get "Access is Denied".
I try to connect from a pc to a SQL Server on another pc. Both pc’s are in a workgroup. I want to connect from a Windows Forms application to a named instance on the other computer. By now I have been able to connect from one pc to SQL Server on the other with tcp:smurfin, 52782.
I want to be able to use servernameinstancename (instead of portnumber) to make a connection in a Windows Forms application.
I’ve checked / tried te following:
•In the properties of the instance, tab Connections, the option Allow Remote Connections is enabled •In Configuration Manager: TCP is enabled •The service SQL Server Browser is started •On the tab IPAddresses, in the section IPAll, there is NO portnumber for TCP Port. And TCP Dynamic Ports has the nummer 52782 •I have created un inbound rule for port 52782 and also for 1434 (SQL Server Browser). And to be on the save side: a rule for 1433 as well. •Restarted the service
If I run the following code in SQL Server, that same port number (52782) is returned:
EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC' GO SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID
While running Sql 2014 upgrade advisor against a 2005 remote sql server. Below is the error im getting:
"Could not populate SQL Instances: System.Security.SecurityException: Requested registry access is not allowed"
Its able to connect SQL 2005 DB server and its also populating all the required Databases, but when i'm tring to click Run at the last step above error is coming. I even installed SQL 2012 upgrade advisor in my system and getting the same above error.
However if i use SQL 2008 upgrade advisor and connect to SQL 2005 server im not getting any error.The tool is generating successfully all the Pre and Post upgrade issues.
I'm running the tool with Run as administrator option. Is there any specific change i need to do in my system so that the tool runs successfully.
I have a user, who is trying log into the server, but everytime he gets this error saying something about the Group policy denies him access.
This user needs access and i'm trying to understand how to grant it to him.
I have been looking into how i can access the group policy editor, but the farthest i can get is the Local group policy editor. How do i make sure this specific user has access?
SQL server job or SP to deny access to an AD login for certain period of time to SQL server instance...i.e. to deny access to login ADxyz from 12 PM to 10 PM and revoke access to same login at 10:01 PM...
I've recently started working with a public sector organisation who have 4 clustered sql instances that has 80% of it's db mirrored.
Looking at the transaction log - it seems that a transaction log backup is a good idea as the log is 4x larger than the data file.But I'm not allowed access to the physical server to check onto which drive I can create the trn. No RDP, no vmware - let's be honest I'm not even allowed to launch cmd line Also the Server Manager informs me "We will need to carefully look at database backups if you guys want to start doing these backups on box, as that will break our off box backup routine (it will screw the transaction chain)."
I don't understand how backing up the transaction log could break the "transaction chain"?
We are upgrading from SQL 2008R2 to SQL2014 but we have discovered that a couple of our applications are not supported on 2014. We'd like to keep one 2008R2 server and one 2014 server until we have time to upgrade the applications and move everything to the new server. The problem is we have custom code in some of the 2014 databases that access tables in the 2008 databases.
I know we can easily do cross server joins by using a linked server, but it would be a huge undertaking to find all that code and add a linked server name in front of every table, stored procedure, etc. So my question is, is there any way to move a database to a different server and still be able to access it without having to qualify the object names with a linked server? Is there some kind of server/database synonym that can be setup that would be recognized by all databases?
I have a Windows Server 2012 R2 2 node cluster with SQL Server 2014 FCI installed. Data files are on a separate Windows Server 2012 R2 file server. Data files share has been permissioned to the SQL Server service and SQL Server Agent service accounts as Full Control. NTFS Permissions are Full Control.
When I try to attach a database CREATE DATABASE AdventureWorksDW2012 ON (FILENAME = 'apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATAAdventureWorksDW2012_Data.mdf') FOR ATTACHI get this error: Msg 5120, Level 16, State 101, Line 4 Unable to open the physical file "apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATAAdventureWorksDW2012_Data.mdf". Operating system error 5: "5(Access is denied.)".
If I log into the file server (called APRICOT) and look at the NTFS permissions they all look good. I have also reapplied the NTFS permissions from the root folder down.
EDIT If I log on to one of the nodes in the cluster as the SQL Server service account and navigate to apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATA and copy and paste the data file, it works fine.
EDIT2: If I log on to the file server and Enable Inheritance at the root level, then Replace all child objects with inheritable permission entries from this object, I get this error:
User Account Control settings on all nodes and the file server are set to Never notify
I created a DTS local package on the SQL Server. It's basically importing a text file into a table in my database. This file resides in a mapped drive (X:) from another server. When I schedule the DTS as a job, it fails. It doesn't execute any of the workflow in the design.
However, when I copy the text file into a drive local to the SQL server (D:), it runs flawlessly.
What I do right now is I have a windows scheduled task that runs a batch file that copies the text file from X: to D: at certain time intervals. Then the job scheduler runs to import it.
What am I missing? How come the job scheduler can't read the file directly from the mapped drive?
I am having a problem with a DTS package that pulls from a flat file off a mapped drive. When the package is ran alone, it runs perfectly but the stored proc that I took from an example from the net will not execute the DTS properly and I am unsure as to why it will not do so.
CREATE PROC spExecuteDTS @Server varchar(255), @PkgName varchar(255), -- Package Name (Defaults to most recent version) @ServerPWD varchar(255) = Null,-- Server Password if using SQL Security to load Package (UID is SUSER_NAME()) @IntSecurity bit = 0,-- 0 = SQL Server Security, 1 = Integrated Security @PkgPWD varchar(255) = ''-- Package Password AS
SET NOCOUNT ON /* Return Values - 0 Successfull execution of Package - 1 OLE Error - 9 Failure of Package */ DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)
-- Create a Pkg Object EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT IF @hr <> 0 BEGIN PRINT '*** Create Package object failed' EXEC sp_displayoaerrorinfo @oPKG, @hr RETURN 1 END
-- Unitialize the Pkg EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize' IF @hr <> 0 BEGIN PRINT '*** UnInitialize failed' EXEC sp_displayoaerrorinfo @oPKG , @hr RETURN 1 END
-- Clean Up EXEC @hr = sp_OADestroy @oPKG IF @hr <> 0 BEGIN EXEC sp_displayoaerrorinfo @oPKG , @hr RETURN 1 END
RETURN @ret GO
that is the stored proc that i am using along with a couple error trapping ones but this being the one that does the actual execution. Is there anything i can change about this in order for it to run the DTS properly from the mapped drive?
From my workstation (with SQL Server 7 Desktop Edition SP3), I seem unable to restore a database on my Server (SQL Server Standard Edition SP3). I am logged into both machines, and I am an Administrator on both machines. Using either a UNC or Mapped Drive (see below)
RESTORE DATABASE ogAEC FROM ogAECDump WITH REPLACE , RECOVERY , STATS , MOVE 'AEC_Data' TO 'Og-sqlsrvrC-DriveMSSQL7DataogAEC_Data.MDF' , MOVE 'AEC_Log' TO 'Og-sqlsrvrC-DriveMSSQL7DataogAEC_Log.LDF'
RESTORE DATABASE ogAEC FROM ogAECDump WITH REPLACE , RECOVERY , STATS , MOVE 'AEC_Data' TO 'Q:MSSQL7DataogAEC_Data.MDF' , MOVE 'AEC_Log' TO 'Q:MSSQL7DataogAEC_Log.LDF'
I get Server: Msg 3156, Level 16, State 2, Line 1 The file 'Og-sqlsrvrC-DriveMSSQL7DataogAEC_Data.MDF' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.
But I am successful if I run the essentially command locally from the server:
RESTORE DATABASE ogAEC FROM ogAECDump WITH REPLACE , RECOVERY , STATS , MOVE 'AEC_Data' TO 'C:MSSQL7DataogAEC_Data.MDF' , MOVE 'AEC_Log' TO 'C:MSSQL7DataogAEC_Log.LDF'
What can I do to be able to restore DBs from my workstation?
I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.
I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.
I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).
So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.
The Secure Sockets Layer (SSL) can be used to encrypt data transferred on our network between our SQL Server instance and a client application. My question is that can I configure Secure Sockets Layer (SSL) encryption on my SQL Server, While this requires a trusted certificate?
We are using Win2k3 R2 with SQL 2000 in a domain environment.
Is it possible to create a domain group to grant admin level and user level access to SQL2000/2005 without giving users server admin or domain admin access?
It has always been my impression that to have admin access to SQL that you had to at least had admin level access on the server.
