February 14, 2008 at 11:41 am
I have a VB 6.0 program that calls an SQL backup at certain intervals with the following code:
sSql = "BACKUP DATABASE Inventory TO DISK = '\\Server\inv\bck080130.bak' WITH FORMAT"
cnConn.CommandTimeout = 0
cnConn.BeginTrans
cnConn.Execute sSql
cnConn.CommitTrans
Usually, I have no problems with this code, but, when in a PC whose user has limited, or not enough rights in the network, it fails with the following error:
-2147217900 Can not open the device backup \\Server\inv\bck080130.bak. Device error or device without connection.
Why is this error generated, if inside the program, the program user (the program user is different from the PC login user) has rights included into the SQL Server.
February 14, 2008 at 11:54 am
This runs inside SQL Server, but I believe that the service account needs rights to the file. Is that the case?
Maybe a network hiccup as well? I never back up to remote disks as it can cause issues. the backup process does not tolerate delays at all.
February 14, 2008 at 12:22 pm
The service account of SQL Server on the Windows Server 2000 has administrative rights, and to the directory backup as well.
If I execute the VB 6.0 program in the server, or in a PC where I log as network administrator, it works.
Only fails if the command comes from a PC whose user has not enough rights in the network.
What I do not understand is, if the VB 6.0 program sends the backup command to the SQL Server, why the limited permissions of that user are passed to the SQL Server.
February 14, 2008 at 12:30 pm
indeed the service account needs write rights to your unc folder \\Server\inv
That's why most of us use a simple sqlagent job to create backups.
Also if I were to use VB6 for this kind of work, I'd use SQLDMO objects.
They are specialy made for this purpose (and are used by EM).
A little study of its object model opens a whole new world:cool:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 16, 2008 at 1:16 pm
Was the user you used to connect to sql server in your connection string in vb has proper permissions? Did you use trusted connection or integrated security?
_____________
Donn Policarpio
February 16, 2008 at 1:29 pm
The user used to log to the SQL Server in the ADO Connection in the VB 6.0 program is the SQL Server administrator.
SQL Server autenthication is used in the security server side.
April 30, 2008 at 5:04 pm
Still unsolved. Here are my latest investigations:
1. If I log on the server machine as the server/lan administrator, and execute the backup program, it works.
2. If I log on a lan PC as the server/lan administrator, it is still failing. The backup program resides in a shared folder in the server, and I call it, for example, as \\SERVER\backprg.exe.
3. All those PCs are reading, writing an deleting records in the SQL server database without problems. The problem is only with the backup process.
4. The folder where the backup files are written is not shared, and total permissions are granted to the administrator and to everybody.
5. The SQL installation was done using MSDE 2000 + SP4 in a Windows Server 2003.
6. In the PC the following error apears (Translated from the original spanish message): -2147217900: Can not open backup device \\SERVER\GESTION\BAKS\MIERCOLES.BAK. Device error or device without connection.
7. Taken from the SQL server log file, I see the following:
2008-04-30 08:17:08.71 spid52 BackupDiskFile::CreateMedia: Backup device '\\SERVER\GESTION\BAKS\MIERCOLES.BAK' failed to create. Operating system error = 5(Acceso denegado.).
2008-04-30 08:17:08.88 backup Error de BACKUP al completar el comando BACKUP DATABASE ArchiMED TO DISK = '\\SERVER\GESTION\BAKS\MIERCOLES.BAK' WITH FORMAT
Any suggestion will be welcome.
April 30, 2008 at 7:41 pm
Is this "server/lan administrator" you're referring to a local account?
Just wanna make sure you're using a domain account to login which has proper permissions on the shared folder. (as well as the sql agent service account).
_____________
Donn Policarpio
May 1, 2008 at 4:47 am
It is the server and lan administrator user.
That is, I use in the PC the administrator user and password that I also type when I log on to the server machine.
I will try to create a new user in the Active Directory, give administrator rights for the server machine, put this user in the list of authorised users in all the required folders, and look at the results.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply